Kwota słownie w SQL

Jak sobie z tym poradzić

Problem zamiany liczby na jej tekstowy odpowiednik nie jest zadaniem trywialnym. Przyjęło się, że na różnych dokumentach, głównie finansowych, oprócz kwoty słownie, pojawia się reprezentujący tę kwotę tekst. Mamy zatem tysiąc dwieście czternaście uzupełniające liczbę 1214, czy też milion dwieście zamiast 1200000. Dla tych, którzy przywiązani są do bazy danych przydałaby się jakaś rozsądna metoda. Nigdzie takiej nie znalazłem, więc postanowiłem zaprezentować swoje rozwiązanie. Odmiana liczebników to jeden z bardziej niewdzięcznych obszarów języka polskiego. Przede wszystkim dlatego, że niewiele jest reguł, które pozwalają automatycznie takie liczebniki wygenerować. Liczby od 0 do 19 i każde pełne dziesiątki po prostu mają swój unikatowy odpowiednik tekstowy. W związku z powyższym nie da się uwspólnić 18 (jeden osiem) i 19 (jeden dziewięć) pomimo tego, że liczby mają po jednej dziesiątce. Ta własność wspólna nic nam nie daje. Mamy osiemnaście i dziewiętnaście. Te składniki (osiemnaście i dziewiętnaście) pojawiają się po raz kolejny dopiero w liczbie... 18000. Niestety, ale nie ma innego wyjścia - sporą cześć wartości słownych należy umieścić w tabeli. Można oczywiście to samo zrobić przy pomocy instrukcji CASE, ale takie rozwiązanie uznałem za moało przejrzyste.

Pomocnicza tabela liczebników

Przyjrzyjmy się zatem wspomnianej tabeli pomocniczej:

CREATE TABLE Liczebniki
(
  Rzad char(1),
  Value int,
  Liczebnik nvarchar(20),
  CONSTRAINT PK_RzadValue PRIMARY KEY CLUSTERED (Rzad,Value)
)

INSERT INTO Liczebniki
VALUES
('J',0,''),
('J',1,' jeden '),
('J',2,' dwa '),
('J',3,' trzy '),
('J',4,' cztery '),
('J',5,' pięć '),
('J',6,' sześć '),
('J',7,' siedem '),
('J',8,' osiem '),
('J',9,' dziewięć '),
('D',0,''),
('D',1,' dziesięć '),
('D',2,' dwadzieścia '),
('D',3,' trzydzieści '),
('D',4,' czterdzieści '),
('D',5,' pięćdziesiąt '),
('D',6,' sześćdziesiąt '),
('D',7,' siedemdziesiąt '),
('D',8,' osiemdziesiąt '),
('D',9,' dziewięćdziesiąt '),
('N',0,' dziesięć '),
('N',1,' jedenaście '),
('N',2,' dwanaście '),
('N',3,' trzynaście '),
('N',4,' czternaście '),
('N',5,' piętnaście '),
('N',6,' szesnaście '),
('N',7,' siedemnaście '),
('N',8,' osiemnaście '),
('N',9,' dziewiętnaście '),
('S',0,''),
('S',1,' sto '),
('S',2,' dwieście'),
('S',3,' trzysta '),
('S',4,' czterysta '),
('S',5,' pięćset '),
('S',6,' sześćset '),
('S',7,' siedemset '),
('S',8,' osiemset '),
('S',9,' dziewięćset '),
('0',1,' tysiąc '),--1
('0',2,' tysiące '),--2-4,M2-M4 gdzie M>=2
('0',5,' tysięcy '),--5-21,M5-M9 gdzie M>=2
('1',1,' milion '),
('1',2,' miliony '),
('1',5,' milionów ')

Trochę dużo tu wartości, ale nic na to nie poradzimy - wartości są unikalne i wynikają wprost ze złożoności naszego języka. Jak można się przekonać, rozpatrujemy wartości rzędu milionów. Przekonamy się wkrótce, że dodanie kolejnych rzędów wielkości nie powinno stanowić problemu. Jak? O tym nieco później.

Parę słów o konstrukcji tabeli. Kolumna Rząd symbolizuje rząd wielkości (tak to sobie nazwałem). J oznacza jedności, D - dziesiątki, N pochodzi od przyrostka -naście, S to setki. Co dalej? Zero oznacza tysiące, jeden miliony (1000 razy większe), 2 mogłoby oznaczać miliardy (1000 razy większe od milionów). Te grupy '1000 razy większe' są już bardziej regularne, dlatego można je umieścić w pętli. Tam mamy do czynienia tylko z trzema formami fleksyjnymi. Aby uprościć algorytm, kolumna Rzad powinna tam mieć zatem kolejne wartości tekstowe, tj. 0, 1, 2 i tak dalej.

Jak wykorzystać tę tabelę?

Sama tabela nic nam jeszcze nie daje. Popatrzmy zatem na funkcję zaprezentowaną na poniższym listingu:

CREATE FUNCTION dbo.KwotaSlownie(@wartosc int)
RETURNS nvarchar(200)
AS
BEGIN
IF @wartosc = 0
  RETURN 'zero'
  
DECLARE @jednosc int = @wartosc % 10
DECLARE @para int = @wartosc % 100
DECLARE @set int = (@wartosc % 1000) / 100;

DECLARE @result nvarchar(200) = ''
IF (@para >= 10 AND @para < 20)
  SET @result =
    (SELECT TOP 1 Liczebnik FROM Liczebniki WHERE Rzad='N' AND Value=@jednosc)
ELSE
BEGIN
  SET @result =
    (SELECT TOP 1 Liczebnik FROM Liczebniki WHERE Rzad='D' AND Value=(@para/10))
    + (SELECT TOP 1 Liczebnik FROM Liczebniki WHERE Rzad='J' AND Value=@jednosc)
END
SET @result =
  (SELECT TOP 1 Liczebnik FROM Liczebniki WHERE Rzad='S' AND Value=@set)
+  @result

-- X > 1000 
DECLARE @mult char(1)='0'
SET @wartosc = @wartosc / 1000;
WHILE @wartosc>0
BEGIN
  SET @jednosc = @wartosc % 10;
  SET @para = @wartosc % 100;
  SET @set = (@wartosc % 1000) / 100;
  
  IF ((@wartosc % 1000) / 10 = 0)
  BEGIN
    SET @result =
      CASE WHEN @jednosc > 1 THEN
        (SELECT TOP 1 Liczebnik FROM Liczebniki WHERE Rzad='J' AND Value=@jednosc)
      ELSE ''
      END
    + CASE WHEN @jednosc=1 THEN
        (SELECT TOP 1 Liczebnik FROM Liczebniki WHERE Rzad=@mult AND Value=1)
      WHEN @jednosc BETWEEN 2 AND 4 THEN
        (SELECT TOP 1 Liczebnik FROM Liczebniki WHERE Rzad=@mult AND Value=2)
      WHEN @wartosc % 1000 != 0 THEN
        (SELECT TOP 1 Liczebnik FROM Liczebniki WHERE Rzad=@mult AND Value=5)
	  ELSE ''
      END
    + @result
  END
  ELSE IF (@para >= 10 AND @para < 20)
  BEGIN
    SET @result =
      (SELECT TOP 1 Liczebnik FROM Liczebniki WHERE Rzad='N' AND Value=@para%10)
    + (SELECT TOP 1 Liczebnik FROM Liczebniki WHERE Rzad=@mult AND Value=5)
    + @result
  END
  ELSE
  BEGIN
    SET @result =
      (SELECT TOP 1 Liczebnik FROM Liczebniki WHERE Rzad='D' AND Value=(@para/10))
    + (SELECT TOP 1 Liczebnik FROM Liczebniki WHERE Rzad='J' AND Value=@jednosc)
    + CASE WHEN @jednosc BETWEEN 2 AND 4 THEN
        (SELECT TOP 1 Liczebnik FROM Liczebniki WHERE Rzad=@mult AND Value=2)
      ELSE
        (SELECT TOP 1 Liczebnik FROM Liczebniki WHERE Rzad=@mult AND Value=5)
      END
    + @result
  END
  SET @result =
    (SELECT TOP 1 Liczebnik FROM Liczebniki WHERE Rzad='S' AND Value=@set)
  +  @result
  SET @wartosc = @wartosc / 1000
  SET @mult = @mult+1
END
RETURN RTRIM(LTRIM(REPLACE(@result,'  ', ' ')))
END

Trochę dużo tego kodu, ale najważniejsze, że spełnia swoją funkcję. Warto wspomnieć, że poszczególne składniki wyniku otoczone są odstępami - stąd konieczność likwidacji podwójnych spacji w środku tekstu wynikowego oraz pojedynczych na brzegach.

Dużo jest też operacji wyszukiwania w tabeli. Dobra wiadomość jest taka, że tabela jest bardzo mała i operacje fizycznego odczytu nie będą stanowić dużego narzutu - będą to głównie operacje odczytu logicznego, bo strony, z pewnością, zaraz na początku, zostaną umieszczone w pamięci operacyjnej - jeżeli ich tam jeszcze nie było.

Przykład wykorzystania fukcji

Aby przetestować funkcję przygotowałem sobie prosty skrypt. Przyjrzyjmy się zatem, jak to działa:

CREATE TABLE #test
(
  Kwota int
)
DECLARE @i int=0
WHILE @i<10
BEGIN
  INSERT INTO #test VALUES
  (ABS(CHECKSUM(NEWID()))%1000000000)
  SET @i += 1
END

SELECT Kwota, dbo.KwotaSlownie(Kwota) Slownie FROM #test
DROP TABLE #test

Skrypt generuje 10 losowych wartości i wrzuca je do tabeli tymczasowej. Ma ona reprezentować tabelę produkcyjną. Następnie wybiera wszystkie wartości tabeli tymczasowej i dodaje do nich wartość zwróconą z ledwo co utworzonej, świeżej funkcji dbo.KwotaSlownie. Popatrzmy na przykładowe wyniki:

Warto wspomnieć, że wyniki na innych komputerach z pewnością będą się różnić - ze względu na funkcję losową.

Ograniczenia - cześć groszowa

Warto wiedzieć, że funkcja nie radzi sobie z wartościami ujemnymi (zwróci NULL) oraz z ułamkami. Co w takim przypadku? Jeżeli są to grosze, to proponuję następujące rozwiązanie:

DECLARE @num decimal(6,2)=3920.50 
SELECT dbo.KwotaSlownie(CAST(@num AS int)) [Slownie zl],
       dbo.KwotaSlownie(CAST(@num*100 AS int)%100) [Slownie gr]

Trik polega na rozdzieleniu części całkowitej od ułamkowej i dwukrotnym wywołaniu funkcji dbo.KwotaSlownie. Rezultat będzie następujący:

Jak poradzić sobie z liczbą ujemną? Zastosować prosty warunek. Jeżeli liczba jest ujemna to do funkcji należy przekazać wartość bezwzględną, a rezultat uzupełnić słowem 'minus '.

Podsumowanie

Mam nadzieję, że skrypt nie zawiera błędów. Oczywiście nie ponoszę również żadnej odpowiedzialności za szkody spowodowane użyciem powyższego skryptu. Jeżeli gdzieś jest błąd, proszę o ślad w komentarzu, postaram się go poprawić.

Kategoria:SQL Server