Wprowadzenie
Tworząc różnego rodzaju funkcjonalności w Comarch ERP XL odnoszące się do bazy danych zachodzi często potrzeba skorzystania z funkcji zwracającej pewną wartość.
Funkcje SQL pełnią rolę "podprogramów" wyliczających pewne dane na podstawie zadanych kryteriów (zwanych parametrami lub argumentami funkcji) i zwracających przeliczony wynik. Używa się ich z kilku powodów:
- Ułatwiają i przyśpieszają kodowanie - zamiast za każdym razem tworzyć rozbudowany kod to można skorzystać z gotowej funkcji. Przykładem takiej funkcji jest CDN.NumerDokumentu zwracającej numer dokumentu handlowego.
- Poprawiają i upraszczają czytelność kodu - zamiast tworzyć zapytanie odwołujące się do wielu tabel można stworzyć zapytanie odwołujące się do jednej tabeli, a dane z tabel "nieobecnych" zostaną pzreliczone przez funkcję). Przykładem takiej funkcji jest CDN.AtrPobierzWartosc zwracającej wartość wskazanego atrybutu, którzy przechowywany jest w zewnętrznej tabeli.
- Unifikują i ujednolicają kod - jeśli pewnego rodzaju przeliczenia (zapytania SQL) mają być wykorzystywane w wielu różnych miejscach to miejsca te powinny odnosić się do funkcji, którą w razie potrzeby łatwo jest zmodyfikować oddziaływując automatycznie na wszystkie te miejsca. Przykładem może być funkcja CDN.Slownie zwracająca liczbę w postaci słownej.
- Zapobiegają błędom powtórzeń rekordów w wynikach - funkcje zawsze zwracają pojedynczą wartość skalarną (poza wyjątkiem funkcji zwaracających recordset), w przypadku łączeń tabel istnieje ryzyko powielenia rekordów. Przykładem znów może być funckja CDN.AtrPobierzWartosc.
- Umożliwiają sięgnięcie do pewnych danych w kontekście, w którym może nie być to możliwe lub trudne. Takie funkcje zwykle tworzy się samodzielnie, na przykład w konieczności sięgnięcia do pewnych danych na elemencie schematu księgowania.
W programie funkcje wykorzystuje się najczęściej w następujących miejscach:
- Wydruki w Crystal Reports
- Raporty SQL i Raporty typu wykres
- Filtry
- Schematy księgowań
- doraźne polecenia SQL zmierzające odczytu pewnych danych z bazy lub do ingerencji w bazę danych
W tym artykule przedstawiam zastosowanie i składnię najczęściej używanych przeze mnie funkcji SQL do pracy na bazie danych ERP XL. Opis dostępnych parametrów funkcji ograniczam do minimum w odniesieniu do zastosowania w ERP XL.
Abyś odniósł z niego jakąś korzyść powinieneś posiadać podstawową wiedzę o SQL.
Jeśli poszukujesz informacji na temat znaczenia dat w programie to przeczytaj artykuł Miejsce i znaczenie dat dokumentów w bazie danych.
DATEADD
Opis
Funkcja zamienia postać liczbową daty na postać czytelną. Daty w bazie danych ERP XL przechowywane są w trzech różnych postaciach, z czego dwie z nich są w postaci liczbowej: data w formacie Clarion i data w formacie TimeStamp.
Format Clarion określa liczbę dni jaka upłynęła od dnia 1800/12/28. Przykładowo liczba 79403 oznacza datę 22.05.2018.
Format TimeStamp określa liczbę sekund jaka upłynęła od dnia 1990/01/01. Przykładowo liczba 895845120 oznacza datę 22.05.2018 godzina 13:52.
Składnia
DATEADD (param1, param2, param3), gdzie parametry przyjmują następującą wartość:
- param1 - w zależności od formatu daty - Clarion: day, TimeStamp: s
- param2 - liczba określająca datę
- param3 - 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ę i godzinę zalogowania się do programu:
select dateadd(s, ses_start, '19900101'),SES_OpeIdent from cdn.sesje
DATEDIFF
Opis
Funkcja zamienia postać czytelną daty na postać liczbową, czyli jest przeciwieństwem funkcji DATEADD. Wykorzystywana najczęściej do konwersji daty na format Clarion, rzadziej na TimeStamp.
Składnia
DATEDIFF (param1, param2, param3), gdzie parametry przyjmują następującą wartość:
- param1 - w zależności od formatu daty - Clarion: day, TimeStamp: s
- param2 - w zależności od formatu daty - Clarion: '18001228', TimeStamp: '19900101'
- param3 - data w postaci czytelnej, w formacie 'YYYYMMDD'
Przykładowe zastosowanie
Zapytanie zwraca listę dokumentów handlowych wystawionych w okresie pomiędzy 1 stycznia 2018, a 15 stycznia 2018:
select * from cdn.tranag where trn_data2 between datediff(day, '18001228', '20180101') and datediff(day, '18001228', '20180115')
Zapytanie zwraca listę sesji, które rozpoczęły się w dniu 22 maja 2018 w godzinach pomiędzy 7, a 9 rano:
select * from cdn.sesje where ses_start between datediff(s, '19900101', '20180522 07:00') and datediff(s, '19900101', '20180522 09:00')
CDN.YMD
Opis
Funkcja dostarczona przez Comarch zamienia 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 ERP XL. Wykorzystywany głównie na rejestrze VAT i na dziennikach księgowych.
Składnia
CDN.YMD (Rok, Miesiac, Dzien)
Przykładowe zastosowanie
Zapytanie wyświetla datę dekretu księgowego:
select CDN.YMD(dzk_rok,dzk_miesiac,dzk_dzien) from cdn.dziennik
FORMAT
Opis
Przydatna funkcja formatująca datę na pożądany format. Dla standardowego ustawienia bazy MS SQL funkcja DATEADD zwraca datę w formacie [Rok-Miesiąc-Dzień Godzina], gdzie w przypadku odwołania do daty w formacie Clarion godzina jest zawsze puta. Funkcja FORMAT pozwala na zmianę sposobu wyświetlania tej daty, w szczególności na usunięcie niepożądanej daty
Składnia
FORMAT(data, format), gdzie parametry przyjmują nastepujące wartości:
- data - bazowa wartość daty
- format - sposób formatowania daty, na przykład: 'dd.MM.yyyy'
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.NumerDokumentu(GIDTyp, SpiTyp, TrNTyp, TrNNumer, TrNRok, TrNSeria, TrNMiesiac), gdzie parametry są zależne od obiektu, do kttó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 dokumentu 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 (bierze w nim również udział 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), gdzie za parametry podstawiane są odpowiednie pola z tabeli CDN.Dziennik lub CDN.Dekrety.
W wersji 2022.1 funkcja została rozszerzona o pole [DZK_OkrSymbol], co stanowi symbol okresu obrachunkowego, który jest yjmowany 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), gdzie 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
Bardzo przydatna funkcja zwracająca wartość wskazanego rodzaju atrybutu na wskazanym obiekcie. Jest niezastąpiona w sytuacji wykorzystania atrybutów na wydruku Crystal Reports i znacząco upraszająca tworzenie zapytań SQL.
Składnia
CDN.AtrPobierzWartosc(GIDTyp, GIDNumer, GIDLp, SubGIDLp, AtkID, TimeStamp), gdzie za parametry mają następujące znaczenie:
- 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. Wykorzystywany głównie w sytuacji umieszczenia atrybutu na pozycji dokumentu. Jesli obiekt nie posiada pozycji to należy wpisać 0
- SubGIDLp - subpozycja obiektu, na którym znajduje się atrybut. Jeśli Wykorzystywany głównie na dostawcach pozycji dokumentu (tabela CDN.TraSElem). Jeśli obiekt nie posiada subpozycji to należy wpisać 0
- AtkID - identyfikator klasy atrybuty. Informacja o identyfikatorze atrybutu przechowywana jest w tabeli klas atrybutów (CDN.AtrybutyKlasy) w polu Atr_AtkID
- TimeStamp - data w formacie TimeStamp, jeśłi atrybut przechowuje wartość historyczną. Wskazanie daty zwraca wartośc atrybutu na wskazany dzień. Jeśli atrybut nie przechowuje wartości hisorycznych to należy wpisać 0
Przykładowe zastosowanie
Zapytanie zwraca wartośc 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 tzw. 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 konieczość odnoszenia się w zapytaniu do tabeli zawierającej porządane informacje. Warto korzystać z niej w sytuacji, gdy w zapytaniu chcemy odwołać się do pewnej tabeli bez łączenia z tą tabelą, na przykład do numeru dokumentu pierwotnego na korekcie, numeru spinacza na WZ, numeru zamówienia na fakturze, nazwy kontrahenta, itp.
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,) gdzie parametry są zależne od obiektu, do którego się odnoszą, a format jest typem bitowym przyjmującym wartość od 0 do 7, gdzie poszczególne bity mają 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-267/08"
- 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
Artykuł uzupełniony o tę funkcję dzięki sugestii pana Michała z CDN Partner.
CDN.DateToClarion
Opis
W wersji 2020.2 ERP XL pojawiła się nowa przydatna funkcja pozwalająca na łatwą konwersję daty sformatowanej do postaci Clarion. Jest ona uproszczonym wariantem funcji DATEDIFF.
Funkcja konwertuje datę kalendarzową do postaci daty Clarion wyrażonej liczbą dni, jaka upłynęła od dnia '1800-12-28'.
Składnia
cdn.DateToClarion('<data kalendarzowa>'), gdzie 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 druga z nowych funkcji dodanych w wersji 2020.2 , która umożliwa na łatwą konwersję daty sformatowanej do postaci TimeStamp. Jest ona uproszczonym wariantem funcji DATEDIFF.
Funkcja konwertuje datę kalendarzową do postaci daty TimeStamo wyrażonej liczbą sekund, jaka upłynęła od dnia '1990-01-01'.
Składnia
cdn.DateToTS('<data kalendarzowa i opcjonalnie godzina>'), gdzie data kalendarzowa jest parametrem tekstowym zawartym w apostrofach.
Przykładowe zastosowanie
Zapytanie do tabeli Sesji pokazuje listę operatorów, którzy w dniu 1 lipca 2020 uruchomili moduł programu po godzinie 9:00.
select Operator=ses_opeIdent, Start=dateadd(s, ses_start, '19900101') from cdn.sesje
where ses_start>cdn.DateToTS('2020-07-01 09:00:00')
Komentarze