Sekwencyjny numer faktury restartowany z nowym rokiem

Witam!

Chce napisac sekwencyjny numer faktury. Zaczynajacy sie od 0000001, 0000002, 0000003....999999. Numer faktury musi restarotowac sie do wartosci 0000001 z kazdym nowym rokiem. Niby proste, ale tez ciekawy problem.

Do tego musze dodac rok przed numerem faktury 2022-0000001 itd. W tabeli invoices mam kolumne paidDate i na niej bede opieral rok, wiec nie zapusuje ponownie roku, normalizacja. Chce zapisac wylacznie sekwenje w osobnej kolumnie.

Zastanawiam sie czy robic w ogole to na poziomie DB czy dac w BE. Jestem w stanie przetestowac oba rozwiazania z unit/integration tests.

Ja z latami przestałem sobie umieć wyobrazić podobnych dokumentów jak faktura, z jedną jedyną warstwą bazy danych, jak za przeproszeniem w borlandach

A skoro mamy jakieś warstwy obiektowo-relacyjne, to nie przeszkadza mi z kodu aplikacyjnego - a i to bardzo późno, w ostatniej chwili podczas zapisu.
Jakby ktoś porzucił fv numer się nie zmarnuje.

Z warstwą jedynie bazodanową nie wyobrażam sobie nie "marnować" numerów

Liczne dodatkowe argumenty:
a) klient zgodnie z prawem będzie chciał odmienną numerację, np też legalną miesięczno/roczną
b) korekcyjne, zaliczkowe
c) przybędzie dokumentów ... horror, zamówienia w innej numeracji niz fv ... jak to rozwiążesz ?

Dla mnie baza danych (w sensie: sama baza danych) nie jest właściwym miejscem do tego

tabela numery z polami rok, typ, numer oraz prosta procedura, która dla podanego roku i typu robi następujące rzeczy:

1. zakłada locka na wierszu z danym rokiem i typem
2. odczytuje bieżący numer z z tego wiersza
3. zwiększa go o jeden
4. aktualizuje wiersz
5. zdejmuje locka
6. w logice musisz jeszcze uwzględnić przypadek, kiedy rekord nie istnieje

Pobieranie numeru robisz w transakcji razem z zapisem dokumentu i nie ma siły, żebyś miał dziury w numeracji albo zdublowane numery jak czegoś nie skopiesz.

poniatowski napisał(a):

Do tego musze dodac rok przed numerem faktury 2022-0000001 itd. W tabeli invoices mam kolumne paidDate i na niej bede opieral rok, wiec nie zapusuje ponownie roku, normalizacja. Chce zapisac wylacznie sekwenje w osobnej kolumnie. >

Akurat zapisywanie numeru formatowanego w osobnej kolumnie moim zdaniem jest warte rozważenia. A co jak klient w roku 2022 chce mieć format FV/2022/00001 natomiast w roku 2023 zechce jeszcze łamać przez miesiąc postaci FV/2022/01/00001 albo w ogóle zmieni numerowanie z rocznego na miesięczne? Nie ogarniesz tego bez zapisywania numeru formatowanego. Trzymanie się za wszelką cenę takich reguł jest czasami ryzykowne ;) Co więcej, raczej numerację powinieneś brać według daty wystawienia dokumentu.

poniatowski napisał(a):

Zastanawiam sie czy robic w ogole to na poziomie DB czy dac w BE. Jestem w stanie przetestowac oba rozwiazania z unit/integration tests.

Ja bym radził zabezpieczyć się na bazie. Błąd w systemie może zawsze się zawsze zdarzyć, jak założysz jakiś klucz unikalny na odpowiednie pola będziesz miał 100% pewności. Chyba nie chcesz by w świat klient puścił dwie różne faktury o tym samym numerze? Widziałem takie przypadki, a wtedy robi się niezbyt miło...

Na razie napisales na szybkosci cos takiego. Ma to jakis sense? Prosze o code review :)

BEGIN WORK;

CREATE OR REPLACE FUNCTION get_next_number()
    RETURNS character varying AS $$
DECLARE next_number VARCHAR;
BEGIN
    LOCK TABLE invoices IN SHARE MODE;

    SELECT format(
               'Invoice-%04s-%04s',
               EXTRACT(year FROM now()),
               to_char(COALESCE(regexp_replace(number, '[^0-9]','','g')::NUMERIC, 0) + 1, 'fm00000')
           ) INTO next_number
      FROM invoices
     WHERE EXTRACT(year FROM CURRENT_DATE) = EXTRACT(year FROM invoice_date)
     ORDER BY id DESC
     LIMIT 1;

    RETURN next_number;
END;
$$ LANGUAGE plpgsql;

INSERT INTO invoices
VALUES((SELECT get_next_number(), 
(...)));

COMMIT WORK;

Moze cos takiego bedzie delikanie lepsze

SELECT format(
            'Invoice-%04s-%04s',
            EXTRACT(year FROM CURRENT_DATE),
            to_char(COALESCE(regexp_replace(number, '[^0-9]','','g')::NUMERIC, 0) + 1, 'fm00000')
       )
 FROM invoices
WHERE invoice_date BETWEEN concat(EXTRACT(year FROM CURRENT_DATE), '-01-01')::date
                       AND concat(EXTRACT(year FROM CURRENT_DATE), '-12-31')::date
ORDER BY 1 DESC
LIMIT 1;

@poniatowski:

Pomedytuj nad tym, co naprawdę sensownie, choć delikatnie i minimalistycznie pisał wyżej @Mr.YaHooo

Dla mnie pisanie kodu o znaczeniu biznesowym (generowanie wysokopoziomowego numeru dokumentu) w SQL to "trochę" się spóźniłeś, o jakieś drobne 15 lat.

Kolejne, jak widzę "invoice" wbudowane w numer, to odzywają się we mnie wszystkie geny przodków z ziemi kieleckiej.
Next, padnie na próbie zrobienia fv korekcyjnej (praktycznie wszyscy chcą na to oddzielnej numeracji), czy zaliczkowej, proformy, które trzeba mieć w systemie w r 2022 itd...
Dorobienie Bardzo Ważnej Faktury 2go stycznia
Łamanie przez oddział / przedstawiciela
Nawet firma Janusz Szmatex Import Export 3ci garaż od lewej tego chce.

@ZrobieDobrze Bylo by latwiej jak bys napisac jak to zrobic. A juz najlepiej jak bys napisal krotki kod. Mozesz jakos dokladniej slowami opisac swoje podejscie?

Czyli generowanie numer w ogole bys nie opieral na bazie danych? lock na tabeli tez bys pominal? Czyli jak bys to zrobil?

Dla mnie ma sense w PHP wywolywac, kazda z ponizszych zapytan. Uwazam, jednak ze ciezko bedzie napisac sekwencyjny numer bez pobierania ostaniego z DB.

BEGIN;

LOCK table (...) ;

SELECT last_number (...) ;

INSERT INTO (...) ;

COMMIT;

Moge przeniesc logike biznesowac z SQL funkcji do PHP klasy. Chociaz moim zadniem oba podejscia sa latwe do zaktualizowania oraz do przetestowania. W peojekcje znajduje sie SQL migracja, wiej jakakolwiek zmiana to dosc prosta sprawa. Do tego moge napisac SQL testy. Trzymanie logiki w tym przypadku na poziomie DB to chyba tylo to, ze delikatnie szybciej sie wykona. Sam nie wiem... dwa podejscia wydaja sie ok.

poniatowski napisał(a):

@ZrobieDobrze Bylo by latwiej jak bys napisac jak to zrobic. A juz najlepiej jak bys napisal krotki kod.

1. Wg mojej wizji nie uciekniesz od choćby częsciowego kodu w języku programowania (jaki ???), by nie mówić o wyższych warstwach, tam gdzie jest wysokopoziomowa wiedza jaki dok biznesowy jest aktualnie robiony. Dla mnie baza to trzymanie do kupy, reguły integralności, ale nie decyzje biznesowe. Podkreślam decyzje, ich implementacja - ale tylko impelmentacja - w SQL nie budzi mojego wielkiego sprzeciwu
2. Adaptacja do typu dokumentu, której nie masz (level master: zmiana budowy numeru dokumentu na nowy rok)
3. w tym konkretnym układ pomiedzy invoice_date a CURRENTDATE dla mnie intuicyjnie jest śliski

poniatowski napisał(a):

Moge przeniesc logike biznesowac z SQL funkcji do PHP klasy.

Zależy co chcesz w tym SQL
O ile trigger, w typowym układzie będzie zbyt słabo wyposażony w wiedzę, na jakiej powinien się oprzeć (typ, schemat numeracji właściwy na dany rok i kontekst biznesowy itd)
Procedura SQL z właściwym zestawem argumentów - lepiej. Ale ja osobiście procedur robiacych coś aktywnego nie lubię, choć przezyję (dwa miejsca kodu)

Powiedzenia "klasa PHP" ... hmmm ... to zależy jaka klasa, jak sie nazywa. Bo klas w ujęciu obiektowym bym widział wiecej, choćby tablica TypDokuemtu[] z której wybierasz jeden.

BTW właśnie testuję myślowo swoją własną hipotezę "co, jeśli z wysokopoziomowej warstwy nie dysponujemy wiarygodnym lockiem" ... katastrofy nie ma. Na potencjalnych konfliktach wychodzi mi najwyżej zmarnowanie numeru (baza OPRÓCZ pola stringowego z formatowanym numerem, ma 3 pola z grupową unikalnością: typ, nr mies (zero dla numeracji rocznej), numer (adekwatnie: w roku / w miesiacu))