/ By Tomasz Trojański / Comarch ERP XL • Wpis Techniczny/ 0 Comments

Najczęściej używane funkcje SQL w Comarch ERP XL

Tworząc dodatkowe funkcjonalności do Comarch ERP XL odnoszące się do bazy danych często potrzeba skorzystać z funkcji zwracającej pewną wartość. Funkcje SQL w Comarch ERP XL można nazwać ‘podprogramami’ które wyliczają pewne dane na podstawie podanych kryteriów i zwracają przeliczone wyniki.

Funkcje SQL w Comarch ERP XL – Dlaczego je używamy?

Podstawą działania systemu Comarch ERP XL są Funkcje SQL. Używa się ich z kilku powodów:

  • Ułatwienie i przyśpieszenie procesu kodowania – zamiast ciągle tworzyć rozbudowany kod można skorzystać z przygotowanej wcześniej funkcji. Przykładem jest NumerDokumentu zwracający numer dokumentu handlowego.
  • Poprawienie i uproszczenie czytelności kodu – zamiast pisać zapytanie odwołujące się do wielu tabel można stworzyć jedno zapytanie. Zapytanie to będzie odwoływać się do jednej tabeli, a dane z tabel ‘nieobecnych’ zostaną przeliczone przez funkcję. Przykładem takowej funkcji jest AtrPobierzWartosc zwracającej wartości wskazanego atrybutu, który przechowywany jest w zewnętrznej tabeli.
  • Unifikacja i ujednolicenie kodu – jeżeli pewnego rodzaju zapytania mają być wykorzystywane w wielu miejscach to miejsca te powinny odnosić się do funkcji, którą w razie potrzeby łatwo można zmodyfikować. Przykładem takiej funkcji będzie Slownie zwracać będzie liczbę w postaci słownej.
  • Zapobieganie błędom powtórzeń rekordów w wynikach – funkcje zawsze zwracają pojedynczą wartość skalarną (wyjątkiem są funkcje zwracające recordset). W przypadku łączenia tabel istnieje ryzyko powielenia rekordów. Przykładem funkcji może być CDNAtrPobierzWartosc.
  • Umożliwiają sięgnięcie do pewnych danych w kontekście, w którym może nie być to możliwe lub trudne. Tego typu funkcje zwykle tworzy się samodzielnie, na przykład w konieczności sięgnięcia do pewnych danych na elemencie schematu księgowania.

Funkcje SQL w Comarch ERP XL wykorzystuje się najczęściej w:

  • Wydrukach,
  • Filtrach,
  • Raportach SQL oraz typu wykres,
  • Schematach księgowań.

Najczęściej używane funkcje SQL w Comarch ERP XL

DATEADD

Opis

Funkcja zamienia postać liczbową daty na postać czytelną. Daty w bazie danych Comarch ERP XL przechowywane są w trzech różnych postaciach. Dwie spośród trzech postaci to postacie liczbowe: format Clarion oraz format TimeStamp.

Format Clarion określa liczbę dni jaka upłynęła od dnia 28.12.1800. Natomiast format TimeStamp określa liczbę sekund jaka upłynęła od dnia 01.01.1990. Poprzez format TimeStamp możemy wykazać dokładną godzinę utworzenia dokumentu.

Składnia

DATEAD (parametr1, parametr2, parametr3)

  • parametr1 – w zależności od formatu daty – Clarion: day, TimeStamp: s,
  • parametr2 – liczba określająca datę,
  • paramet3 – w zależności od formatu daty – Clarion: ‘18001228’, TimeStamp: ‘19900101’.

Przykładowe zastosowanie

Zapytanie zwraca datę wystawienia dokumentu handlowego:

select dateadd(day, trn_data2, '18001228') from cdn.tranag

Zapytanie zwraca datę oraz godzinę zalogowania do programu:

select dateadd(s, ses_start, '19900101'),SES_OpeIdent from cdn.sesje

DATEDIFF

Opis

Funkcja zmienia postać czytelną daty w postać liczbową. Funkcja DATEDIFF jest przeciwieństwem wyżej opisanej funkcji DATEADD. Wykorzystuje się ją najczęściej do konwersji daty na format Clarion, rzadziej natomiast na format TimeStamp.

Składnia

DATEDIFF (parametr1, parametr2, parametr3)

  • parametr1 – w zależności od formatu daty – Clarion: day, TimeStamp: s,
  • parametr2 – w zależności od formatu daty – Clarion: ‘18001228’, TimeStamp: ‘19900101’,
  • parametr3 – data w postaci czytelnej, w formacie ‘YYYYMMDD’.

Przykładowe zastosowanie

Zapytanie zwraca listę dokumentów handlowych wystawionych w okresie pomiędzy 1 stycznia 2023 a 30 stycznia 2023:

select * from cdn.tranag
where trn_data2 between datediff(day, '18001228', '20230101') and datediff(day, '18001228', '20230130')

Zapytanie zwraca listę sesji, które rozpoczęły się w dniu 20 lutego 2023 w godzinach pomiędzy 10 a 12:

select * from cdn.sesje
where ses_start between datediff(s, '19900101', '20230220 10:00') and datediff(s, '19900101', '20230220 12:00')

CDN.YMD

Opis

Funkcja dostarczona przez Comarch zmienia datę zapisaną w trzech oddzielnych polach (Rok, Miesiąc, Dzień) na datę w postaci skalarnej, czytelnej. Jest to trzeci spośród dwóch stosowanych sposobów zapisu daty w bazie Comarch ERP XL. Format daty jest wykorzystywany głównie na rejestrach VAT oraz na dziennikach księgowych.

Składnia

CDN.YMD (Rok, Miesiąc, Dzień)

Przykładowe zastosowanie

Zapytanie wyświetla datę dekretu księgowego:

select CDN.YMD(dzk_rok, dzk_miesiac, dzk_dzien) from cdn.dziennik

FORMAT

Opis

Funkcja formatująca datę na pożądany format. Dla standardowego ustawienie bazy MS SQL funkcja DATEADD zwróci datę w formacie [Rok-Miesiąc-Dzień Godzina]. W przypadku odwołania do daty w formacie Clarion godzina zawsze jest pusta. Funkcja pozwala na zamianę sposobu wyświetlania daty, w szczególności na usunięcia niepożądanej daty.

Składnia

FORMAT(data, format)

  • data – bazowa wartość daty,
  • format – sposób formatowania daty (np. ‘DD.MM.YYYY’, ‘YYYY.MM.DD’).

Przykładowe zastosowanie

Zapytanie zwraca datę wystawienia dokumentu handlowego w polskim formacie:

select format(dateadd(day, trn_data2, '18001228'), 'dd.MM.yyyy')
from cdn.tranag

CDN.NumerDokumentu

Opis

Funkcja zwraca wewnętrzny numer dokumentu, przy czym obsługuje różne typy dokumentów w XL-u. Są to m.in: dokumenty handlowe, magazynowe, importowe, zamówienia i oferty, bilans otwarcia, noty memoriałowe, wyciągi bankowe i inne.

Składnia

CDN.NumerDokumnetu(GIDTyp, SpiTyp, TrNTyp, TrNNumer, TrNRok, TrNSeria, TrNMiesiac)

Parametry są zależne od obiektu, do którego się odnoszą.

Przykładowe zastosowanie

Zapytanie zwraca numer dokumentu handlowego:

select CDN.NumerDokumentu(TrN_GIDTyp, TrN_SpiTyp, TrN_TrNTyp, TrN_TrNNumer, TrN_TrNRok, TrN_TrNSeria, TrN_TrNMiesiac)
from CDN.TraNag

Zapytanie zwraca numer dokumentowego magazynowego:

select CDN.NumerDokumentu(Man_GIDTyp, 0, MaN_TrNTyp, MaN_TrNNumer, MaN_TrNRok, MaN_TrNSeria, MaN_TrNMiesiac)
from CDN.MagNag

Zapytanie zwraca numer zamówienia lub Oferty (zawarta jest w nim również funkcja CDN.DokMapTypDokumentu generująca identyfikator obiektu dla tabeli CDN.ZamNag):

select CDN.NumerDokumentu(CDN.DokMapTypDokumentu(ZaN_GIDTyp,ZaN_ZamTyp,ZaN_Rodzaj),0,0,ZaN_ZamNumer,ZaN_ZamRok,ZaN_ZamSeria,ZaN_ZamMiesiac)
from cdn.ZamNag

Zapytanie zwraca numer zapisu w rejestrze bankowym:

select CDN.NumerDokumentu(KAZ_GIDTyp, 0, 0, KRP_Numer, KAZ_Rok, KAZ_Seria, KAZ_KRPLp)
from CDN.Zapisy
join CDN.Raporty ON KAZ_KrpNumer = KRP_GIDNumer

CDN.NumerDekretu

Opis

Funkcja zwraca numer zapisu dekretu cząstkowego lub numer w dzienniku.

Składnia od wersji 2022.1

Cdn.numerDekretu(dzk_bufor, dzk_dziennik, dzk_rok, dzk_miesiac, dzk_rmNumer, dt_gidlp, DZK_Prosty, DZK_OkrSymbol)

Za parametry podstawione są odpowiednie pola z tabeli CDN.Dziennik lub CDN.Dekrety.

W wersji 2022.1 funkcja została rozszerzona o pole [DZK_OkrSymbol], cl stanowi symbol okresu obrachunkowego, który jest ujmowany w numerze dekretu.

Składnia wcześniejsza do wersji 2022.0

Cdn.numerDekretu(dzk_bufor, dzk_dziennik, dzk_rok, dzk_miesiac, dzk_rmNumer, dt_gidlp, dzk_prosty)

Za parametry podstawiane są odpowiednie pola z tabeli CDN.Dziennik lub CDN.Dekrety

Przykładowe zastosowanie

Zapytanie zwraca numer księgowego dekretu cząstkowego (wraz z numerem pozycji dekretu przekazywanego w polu dt_gidlp):

select cdn.numerDekretu(dzk_bufor, dzk_dziennik, dzk_rok, dzk_miesiac, dzk_rmNumer, dt_gidlp, DZK_Prosty, DZK_OkrSymbol)
from cdn.dziennik
join cdn.dekrety on dt_gidnumer=dzk_gidnumer

Zapytanie zwraca numer w dzienniku księgowym ( z pominięciem numeru cząstkowego):

select cdn.numerDekretu(dzk_bufor, dzk_dziennik, dzk_rok, dzk_miesiac, dzk_rmNumer, 0, 1,DZK_OkrSymbol)
from cdn.dziennik

CDN.AtrPobierzWartosc

Opis

Funkcja zwraca wartość wskazanego rodzaju atrybutu na wskazanym obiekcie. Funkcja jest niezastąpiona w pracy z Crystal Reports znacząco upraszczając tworzenie zapytań SQL.

Składnia

CDN.AtrPobierzWartosc( GIDTyp, GIDNumer, GIDLp, SubGIDLp, AtkID, TimeStamp)

Parametry prezentują się następująco:

  • GIDTyp – GIDTyp obiektu, na którym znajduje się atrybut,
  • GIDNumer – GIDNumer obiektu, na którym znajduje się atrybut,
  • GIDLp – GIDLp obiektu, na którym znajduje się atrybut. Parametr wykorzystywany głównie w sytuacji umieszczenia atrybutu na pozycji dokumentu. Jeśli obiekt nie posiada pozycji to należy wpisać 0,
  • SubGIDLp – subpozycja obiektu, na którym znajduje się atrybut. Jest wykorzystywany głównie na dostawcach pozycji dokumentu (tabela CDN.TraSEIem). Jeśli obiekt nie posiada pozycji to należy wpisać 0,
  • AtkID – identyfikator klasy atrybutu. Informacja o identyfikatorze atrybutu przechowywana jest w tabeli klas atrybutów (CDN.AtrybutyKlasy) w polu Atr_AtkID,
  • TimeStamp – data w formacie TimeStamp, jeśli atrybut przechowuje wartość historyczną. Wskazanie daty zwraca wartość atrybutu na wskazany dzień. Jeśli obiekt nie posiada pozycji to należy wpisać 0.

Przykładowe zastosowanie

Zapytanie zwraca wartość atrybutu określonego identyfikatorem 4 (i nie będącego atrybutem przechowującym wartość historyczną) na liście dokumentów handlowych:

select cdn.AtrPobierzWartosc(trn_gidtyp, trn_gidnumer, 0, 0, 4, 0) from cdn.traNag

OBJECT_NAME

Opis

Wbudowana w MS SQL funkcja sięgająca do metadanych. Zwraca nazwę obiektu bazy danych o wskazanym identyfikatorze. Może to być nazwa tabeli, funkcji, procedury, itd. Jest zwłaszcza pomocna przy identyfikowaniu nazwy tabeli dla wskazanej kolumny.

Składnia

Object_name(ID), gdzie ID określa identyfikator obiektu w bazie danych.

Przykładowe zastosowanie

Zapytanie zwraca nazwę tabeli dla kolumny ‘Knt_GIDNumer’:

select object_name(ID) from syscolumns where name='Knt_GIDNumer'

CDN.NazwaObiektu

Opis

Funkcja zwraca wewnętrzny numer dokumentu (podobnie jak CDN.NumerDokumentu) przy czym ze względu na uproszczone parametry eliminuje konieczność odnoszenia się w zapytaniu do tabeli zawierającej pożądane informacje. Warto skorzystać z niej w sytuacji, gdy chcemy odwołać się do pewnej tabeli bez łączenia z tą tabelą. Dodatkowo obsługuje dane słownikowe, w tym: kartoteki towarów, kontrahentów, środków trwałych i pracowników.

Składnia

CDN.NazwaObiektu(GIDTyp, GIDNumer, GIDLp, Format)

Parametry są zależne od obiektu, do którego się odnoszą, a format jest typem bitowym przyjmującym wartość od 0 do 7. Poszczególne bity maja następujące znaczenie:

  • 1 – rodzaj obiektu, np. „Przyjęcie zewnętrzne”,
  • 2 – numer obiektu lub akronim w przypadku obiektów słownikowych, np. „PZ-232/03”,
  • 4 – nazwa obiektu w przypadku obiektów słownikowych, np. nazwisko pracownika.

Przykładowe zastosowanie

Zapytanie do jednej tabeli CDN.TraNag zwraca informacje przechowywane w innych tabelach, w tym ZamNag, OpeKarty, KntKarty i skorelowanej tabeli (na potrzeby korekty) TraNag:

select
[Nr dokumentu]=CDN.NumerDokumentu(TrN_GIDTyp, TrN_SpiTyp, TrN_TrNTyp, TrN_TrNNumer, TrN_TrNRok, TrN_TrNSeria, TrN_TrNMiesiac),
[Nr korekty]=CDN.NazwaObiektu(TrN_ZwrTyp,TrN_ZwrNumer,0,2),
[Nr Zamówienia]=CDN.NazwaObiektu(TrN_ZanTyp,TrN_ZanNumer,0,2),
[Kontrahent]=CDN.NazwaObiektu(TrN_KntTyp,TrN_KntNumer,0,4),
[Operator wystawiający]=CDN.NazwaObiektu(TrN_OpeTypW,TrN_OpeNumerW,0,4)
FROM CDN.TraNag WHERE TrN_GIDTyp=2033 and TrN_ZwrTyp=2041

CDN.DateToClarion

Opis

W Comarch ERP XL 2020.2 pojawiła się nowa przydatna funkcje SQL pozwalająca na łatwą konwersję daty sformatowanej do postaci Clarion.  Jest ona uproszczonym wariantem funkcji DATEDIFF. Funkcja konwertuje datę kalendarzową do postaci daty Clarion wyrażonej liczby dni, jaka upłynęła od dnia ‘1800-12-28’.

Składnia

Cdn.DateToClarion(‘<data kalendarzowa>’)

Data kalendarzowa jest parametrem tekstowym zawartym w apostrofach.

Przykładowe zastosowanie

Zapytanie do tabeli TraNag zawęża listę dokumentów do określonego zakresu dat:

select [Nr dokumentu]=CDN.NumerDokumentu(TrN_GIDTyp, TrN_SpiTyp, TrN_TrNTyp, TrN_TrNNumer, TrN_TrNRok, TrN_TrNSeria, TrN_TrNMiesiac)
FROM CDN.TraNag
WHERE TrN_GIDTyp=2033 and TrN_Data2 between cdn.DateToClarion('2020-02-01') AND cdn.DateToClarion('2020-03-15')

CDN.DateToTS

Opis

To kolejna z nowych funkcje SQL w Comarch ERP XL 2020.2, która umożliwia na łatwą konwersję daty sformatowanej do postaci TimeStamp. Jest ona uproszczonym wariantem funkcji DATEDIFF. Funkcja konwertuje datę kalendarzową do postaci daty TimeStamp wyrażonej liczbą sekund, jaka upłynęła od dnia ‘1990-01-01’.

Składnia

Cdn.DateToTS(‘<data kalendarzowa i opcjonalnie godzina>’)

Data kalendarzowa jest parametrem tekstowym zawartym w apostrofach.

Przykładowe zastosowanie

Zapytanie do tabeli Sesji pokazuje listę operatorów, którzy w dniu 10 sierpnia 2023 uruchomili moduł programu o 12:00.

select Operator=ses_opeIdent, Start=dateadd(s, ses_start, '19900101') 
from cdn.sesje
where ses_start>cdn.DateToTS('2023-08-10 12:00:00')

Zapraszamy na darmową prezentację Comarch ERP XL. Skontaktuj się z nami!
Telefonicznie 797 604 592 lub mailowo.

Jeżeli są Państwo zainteresowani innymi ułatwieniami w prowadzeniu firmy.
Zapraszamy do naszego sklepu!