W tym artykule prezentujemy kilka przykładów użycia zapytań SQL do rozwiązania konkretnych problemów. Zapytania z tego artykułu zostały przetestowane, można je przepisać i używać we własnych systemach. Szczegółowy opis zastosowanych konstrukcji oraz użytych tabel pozwala na samodzielną modyfikację zapytań do własnych potrzeb.
Pobieranie statusu dokumentu
Jednym z najczęściej używanych zapytań jest odpytywanie bazy o statusy dla konkretnego typu dokumentu. Jeżeli chcemy zbudować filtr na liście, podobny do tego:
musimy użyć zapytania podobnego do poniższego.
Statusy dokumentów definiowane są dla typów dokumentów i są zapisane w tej samej tabeli – DoPa. Dlatego przeszukujemy tę tabelę. Pobieramy nazwę statusu jako identyfikator (kolumna ID) oraz ikonę statusu, ustawioną w konfiguracji. Możemy użyć do tego wbudowanej funkcji fn_Get_Ic_ById. Funkcja wymaga podania nazwy ikony oraz tekstu, który będzie wyświetlany w tooltipie po najechaniu na ikonę. W naszym przykładzie podajemy tam nazwę statusu.
Musimy znaleźć tylko te statusy, które odpowiadają naszemu typowi dokumentu. Jak wspomnieliśmy wcześniej, zarówno typ dokumentu jak i status dokumentu zapisane są w tej samej tabeli. Kolumna Parent, zawierająca ID dokumentu nadrzędnego, mówi nam o powiązaniach między rekordami. Wystarczy wyszukać te rekordy, które w kolumnie Parent mają wartość ID naszego typu dokumentu. Do tego służy nam konstrukcja JOIN DoPa DoPaP ON DoPa.Parent = DoPaP.ID – wiążemy typ dokumentu z jego statusami. Po dodaniu warunku WHERE DoPaP.GUID = 'F5B91AB3-159B-4E33-AA91-273F396CE20B’ ograniczamy się do jednego typu dokumentu. Guid typu dokumentu możemy znaleźć w jego konfiguracji:
Wystarczy użyć wartości skopiowanej z tego miejsca.
Do pełnego działania naszego zapytania trzeba jeszcze dołączyć tabelę zawierającą kolory ikon. Robimy to poprzez konstrukcję LEFT JOIN Ic ON DoPa.IcID = Ic.ID. Wartość z tej tabeli użyjemy do funkcji wstawiającej ikonę opisaną wcześniej.
Na koniec warto zadbać o to, b nie dostać elementów usuniętych z systemu. Do tego użyjemy warunku DoPa.Deleted = 0. Możemy też posortować wyniki. W tym przypadku po kolumnie Order, która mówi nam o kolejności statusów.
Struktura organizacyjna wraz z użytkownikami
Kolejną użyteczną konstrukcją jest zapytanie zwracające elementy struktury organizacyjnej wraz z użytkownikami przypisanymi do stanowisk. Oczywiście zamiast tego możemy użyć po prostu danych z tabeli zawierającej użytkowników (Us) albo elementy struktury (St). Może to jednak nie wystarczyć. Często, np. w procedurze obiegu, potrzebujemy stanowisko a nie użytkownika. Potrzebujemy wskazać cały dział a nie chcemy wypisywać wszystkich użytkowników z tego działu. Stanowiska też są nie do końca wygodne. Jak rozróżnić, który z pięciu Młodszych programistów jest tym, którego chcemy wskazać? Chcemy osiągnąć taki efekt:
Należy do tego skonstruować zapytanie, które powiąże użytkownika ze strukturą organizacyjną i właściwie wyświetli ich opis w zależności od poziomu struktury. Możemy użyć do tego takiego zapytania:
W zapytaniu wychodzimy od tabeli St czyli tabeli zawierającej strukturę organizacyjną firmy. Relacje pomiędzy stanowiskiem a użytkownikiem przechowywane są w tabeli UsSt. To druga tabela, którą użyjemy w zapytaniu. Jak widać nazwa tabeli, która składa się z nazw tabel St oraz Us mówi nam, że w tej tabeli przechowywane są relacjie między tabelami. To jest reguła budowania nazw tabel w bazie danych systemu NAVIGATOR, tabele relacyjne zawsze w nazwie mają nazwy tabel wchodzących w składa relacji. W końcu trzecia tabela, US, w której przechowywani są użytkownicy. Będziemy jeszcze potrzebować tabeli StPa w której przechowywany jest wzorzec struktury organizacyjnej. Ta tabela posłuży nam do rozróżnienia stanowiska od innych elementów struktury organizacyjnej.
W zapytaniu użyta jest konstrukcja warunkowej CASE WHEN … THEN … ELSE … END. Posłuży ona nam do wstawienia różnych wartości w zależności od elementu struktury organizacyjnej i przypisania użytkownika do struktury organizacyjnej. Po użyciu tej konstrukcji system sprawdza warunek zapisany po słowie kluczowym WHEN i jeżeli jest prawdziwy wykonywany jest kod po słowie kluczowym THEN. Specjalne słowo kluczowe ELSE służy do wskazania kodu, który zostanie wykonany jeżeli żaden z warunków po WHEN nie jest prawdziwy. Należy pamiętać by całą konstrukcję rozpocząć od CASE i zakończyć na END. Bez tych słów kluczowych zapytanie zakończy się błędem.
Warunek StPa.StLeID = 5 sprawdza, na jakim poziomie wzorca struktury organizacyjnej znajduje się aktualny element. W naszym przypadku sprawdzamy, czy jest to poziom o ID=5 czyli stanowisko. Poziomy struktury można sprawdzić w tabeli StLe zapytaniem SELECT * FROM StLe. Mamy dwa przypadki gdy ten warunek jest prawdziwy – stanowisko jest obsadzone lub nie. Możemy tutaj użyć cechy złączenia zewnętrznego (LEFT JOIN), jeżeli nie ma relacji między rekordami to wartość kolumny jest pusta (NULL). Stąd warunek, Us.ID>0 – stanowisko obsadzone, Us.ID IS NULL – stanowisko nieobsadzone.
Do wyświetlenia wartości używamy standardowego operatora łączenia stringów. W przypadku stanowiska obsadzonego łączymy stringi zapytaniem: St.Name + ’ (’ + Us.Name + ’ ’ + Us.Surname + ’)’ – używamy nazwy stanowiska z tabeli St oraz imienia i nazwiska z tabeli Us. Analogicznie dla stanowisk nieobsadzonych, z tym, że zamiast imienia nazwiska wstawiamy napis „nieobsadzone”: St.Name + ’ (nieobsadzone)’
Dla innych elementów struktury organizacyjnej wstawiamy nazwę elementu oraz napis „wszyscy” co użytkownik zobaczy jako np. „Dział controllingu (wszyscy)”.
Do łączenie tabel używamy złączenia zewnętrznego (LEFT JOIN) co gwarantuje nam, że element z lewej strony (u nas z tabeli St) zawsze będzie wstawiany do wyniku zapytania. Jeżeli będzie istniał element z prawej strony to zostanie on wstawiony, jeżeli nie to wstawiona będzie wartość NULL. Użyliśmy tej cechy przy sprawdzaniu, czy stanowisko jest obsadzone.
Każde złączenie musi zawierać warunek który wskaże, które elementy łączymy ze sobą. Jeżeli nie będzie warunku to połączymy każdy element z każdym. W naszym przypadku łączymy tabele po kolumnach zawierających ID elementów. Jedną z cech bazy danych systemu NAVIGATOR jest to, że nazwy kolumn służących łączenia zinnymi tabelami zawierają nazwy tabeli i kolumn z którymi się łączą. Najlepiej pokazać to na przykładzie:
LEFT JOIN UsSt ON St.ID = UsSt.StID
Łączymy tabelę St z tabelą UsSt. Tabela UsSt jest po prawiej stronie więc nie musi zawierać pasującego rekordu. W warunku mamy St.ID czyli kolumną z tabeli St o nazwie ID która ma się równać UsSt.StID – kolumnę z tabeli UsSt o nazwie StID. Jak widzimy po nazwie możemy rozpoznać że kolumna StID odpowiada kolumnie St.ID (przy okazji, należy uważać na kropki w nazwach). Jeżeli te wartości są równe to znaczy że elementy są w relacji. Dzięki temu łatwo można znaleźć tabele relacyjne oraz klucze relacji.
Tabele łączymy kaskadowo, najpierw St z StUs a następnie StUs z Us. Jeżeli nie ma relacji to dostaniemy wartość pustą. Jest to typowa konstrukcja nagminnie wykorzystywana w systemie NAVIGATOR. Nie musimy się ograniczać do trzech tabel, kaskada łączeń może być dłuższa.
Na koniec warto pamiętać o elementach usuniętych. Nie chcemy mieć w wynikach tego zapytania usuniętych stanowisk czy osób które już nie pracują na danym stanowisku. Ograniczamy to klauzulą Deleted=0. Większość tabel w systemie NAVIGATOR posiada kolumnę Deleted, dlatego trzeba uważać i odwoływać się do właściwej tabeli. W powyższym przykładzie sprawdzamy usunięcie elementu w dwóch tabelach: St czyli stanowiska i StUs czy użytkownikiem (zatrudnienie). Dlaczego nie sprawdzamy czy użytkownik jest usunięty? ponieważ system pilnuje by przy usuwaniu użytkownika nie był on przypisany do żadnego stanowiska więc wystarczy sprawdzić relację w tabeli StUs.
Stan procesu w dokumentach powiązanych
Częstym problemem w systemach obiegu dokumentów jest sprawdzenie, czy zakończyły się procesy uruchomione na dokumentach powiązanych z danym dokumentem. Przykładem może być Zapotrzebowanie czekające na realizację faktury czy Zlecenie transportowe czekające na Kartę drogową. Dysponując odpowiednią procedurą oraz akcjami Czekaj oraz Zmień wartość pola można stworzyć obieg, który będzie czekał w pętli na zakończenie powiązanych dokumentów.
Powiązanie poprzez elementy
Jedną z metod powiązania dokumentów jest powiązanie poprzez elementy na dokumencie. Jeżeli tworzymy jeden dokument z innego poprzez przekształcenie to system automatycznie tworzy relacje między elementami na tych dokumentach. Pozwala to na zbudowanie procedury która będzie sprawdzała, co się dzieje z tymi dokumentami. Co ważne, same dokumenty nie muszą być w relacji z dokumentem który będzie czekał. Korzystając z tej metody można sprawdzać różne dokumenty, inne dla każdej pozycji elementów.
Podobnie jak w poprzednim przykładzie, będziemy tu używać złączeń zewnętrznych (LEFT JOIN) z tym, że tym razem użyjemy znacznie więcej tabel. Potrzebujemy przejść od elementu na formularzu do procedury obiegu na dokumencie skojarzonym poprzez elementy.
Użyjemy tutaj też klauzuli exists która sprawdza, czy zapytanie zwróciło jakiś rekord. Dla naszych potrzeb wystarczy wiedza, czy są niezakończone procesy. Jeżeli potrzebna jest lista dokumentów to można łatwo zmodyfikować to zapytanie usuwając klauzule SELECT CASE oraz EXISTS i pozostawiając samo zapytanie wewnętrzne dostosowując kolekcjękolumn po SELECT do własnych potrzeb.
W naszym zapytaniu będziemy korzystać z następujących tabel:
Do – tabela zawierająca dokumenty
Ra – tabela zawierająca relacje między obiektami. Obiektem może być dokument, zadanie, element itp.
DoEl – tabela zawiera relacje między dokumentem a elementem. Na przykład między zamówieniem a zamówionym asortymentem
Do – czasami potrzebujemy dołączyć kilka razy tę samą tabelę szukając innych dokumentów.
Wo – tabela zawierająca procedury obiegu
WoSa – tabela zawierająca statusy procedur obiegu.
Tak jak w poprzednim przykładzie, tu również łączymy tabele kaskadowo. Używamy w tym przykładzie takich warunków łączenia:
left join DoEl element1 on element1.DoID=Do.ID – dołącz element tabeli DoEl (czyli Elementy na dokumencie) dla konkretnego dokumentu korzystając z kolumny DoID w tabeli DoEl i kolumny ID w Do
left join Ra rael on element1.ID=rael.TableID – dołącz elementy z tabeli relacji. Szukamy tu obiektów typu Element które są w relacji z innymi obiektami typu element. To ograniczenie realizujemy warunkiem rael.RelationTbID=50, konkretną wartość dla tabeli można sprawdzić zapytaniem SELECT * FROM Tb i poszukać rekordu o wartości DoEl w kolumnie Table. Tabela Ra ma dwa zestawy podobnych kolumn: TableID i TbId k tóre odpowiednio wskazują ID obiektu i ID tabeli pierwszego składnika relacji oraz RelationTableID i RelationTbID które odnosi się do drugiego składnika relacji. Zauważmy, że w tym, zapytaniu łączymy elementy według pierwszego składnika relacji (TableID) ale sprawdzamy do której tabeli należy drugi składnik relacji (RelationTbID).
left join DoEl parel on parel.ID=rael.RelationTableID and parel.Deleted=0 – dołączamy drugi raz tabelę DoEl, tym razem chcemy dołączyć elementy, które są w relacji z elementami na bazowym dokumencie. Posługujemy się tabelą Ra z której bierzemy ID obiktu będącego drugim składnikiem relacji (RelationTableID).
left join Do pardoc on pardoc.ID=parel.DoID and pardoc.Deleted=0 – dołączamy tabelę z dokumentami. Szukamy dokumentów, które zawierają elementy będące w relacji z elementami z bazowego dokumentu. Szukamy takich dokumentów, których ID jest równe DoID z tabeli DoEl. Dodatkowo sprawdzamy, czy dokument nie został usunięty.
left join Wo wkfl on wkfl.TableID=pardoc.ID – dołączamy do naszego zapytania informacje o obiegu. Szukamy takich procedur obiegu, które są powiązane z naszymi dokumentami z poprzedniego złączniea (warunek wkfl.TableID=pardoc.ID)
left join WoSa on WoSa.ID=wkfl.WoSaID – ostatnie złączenie, które tak naprawdę nie jest potrzebne do naszego zapytania ale poprawia czytelność wyników jeżeli zamiast sprawdzenia czy istnieją niezakończone obiegi chcemy je wyświetlić. Tabela WoSa zawiera statusy procedur obiegu. W tabeli Wo, w której przechowywane są biegi, jest kolumna WoSaID w której przechowywane jest ID statusu. Dołączając tę tabelę możemy zamiast ID statusu wyświetlić jego nazwę (WoSa.Name)
Ostatniąrzeczą, którą trzeba zrobić, żeby zapytanie mogło być poprawnie wykonane należy wskazać dokument, dla którego chcemy sprawdzić zależności. Można to zrobić warunkiem where Do.ID=@DocumentID gdzie @dokumentID jest zmienną. Można ją przypisać do dowolnego pola na formularzu lub zmiennej systemowej poprzez funkcję Generuj parametry.
Tak skonstruowane zapytanie zwróci nam listę wszystkich procesów, które są uruchomione na dokumentach powiązanych z naszym dokumentem poprzez elementy. Przykładowe wykonanie zapytania wygląda tak:
Pierwsze trzy kolumny opisują nam dokument bazowy i odpowiadają fragmentowi Do.ID as DocID, Do.PersonalNumber,Do.Description w zapytani. Kolejne dwie asortyment na tym dokumencie bazowym (element1.AsID as AsID, element1.AsName). Kolejne kolumny wynikają z dołączania kolejnych tabel: Ra (rael.RelationTbID, rael.RelationTableID), DoEl (,parel.Deleted as pareldel, parel.DoID as pareldoid), Do (pardoc.PersonalNumber, pardoc.Deleted as DocDeleted), Wo (wkfl.Deleted as WoDeleted, wkfl.WoSaID) i w końcu WoSa (WoSa.Name)
Możemy teraz przeanalizować dane, które zwróciło to zapytanie. Najprościej jest z Kopertą c5 – kolumna RelatoinTbID jest pusta czyli nie ma żadnej relacji do tego elementu. Możemy powiedzieć, że w systemie niezostał utworzony dokument powiązany z tym elementem, jeżeli jest to zapotrzebowanie to nic nie zostało zrobione by to zapotrzebowanie zrealizować. Jeżeli mamy taką sytuację to powinniśmy uwzględnić tn rekord w naszym zapytaniu – nie ma dokumentu, nie ma realizacji więc system powinien poczekać, aż realizacja się pojawi.
Dla pozostałych elementów mamy wpisy w których RelationTbID jest niepuste. Interesują nas tylko te rekordy, które odnoszą się do innych elementów czyli RelationTbID=50. Doceolowo mamy taki warunek: (rael.RelationTbID is null or rael.RelationTbID=50) – chcemy dostać te rekordy, które nie mają powiązań z innymi obiektami lub mają powiązania z elementami. Efekt tego zapytania wygląda tak:
Widzimy coś niepokojącego w pierwszej linii – mamy ID obiektu, z którym nasz element jest w relacji ale pozostałe kolumny są puste. To pozostałość po usuwaniu elementów z dokumentu. System nie usuwa wpisów z tabeli Ra w takim przypadku więc musimy je odfiltrować ręcznie warunkiem pardoc.PersonalNumber is not null.
Popatrzmy teraz na drugi rekord. Mamy tu dane dokumentu (ID, PersonalNumber) ale w kolumnie dotyczącej obiegu mamy NULL. Oznacza to, że dokument nie ma zdefiniowanej procedury obiegu. Możemy przyjąć, że samo pojawienie się dokumentu w systemie kończy proces przetwarzania tego elementu.
Jeżeli proces istnieje w systemie to należy sprawdzić czy nie jest usunięty oraz jaki jest jego status. Odrzucamy te rekordy, które są usunięte lub zakończone. Ostatecznie nasz warunek wygląda tak: (rael.RelationTbID IS NULL OR (pardoc.PersonalNumber IS NOT NULL OR wkfl.WoSaID<>4 AND wkfl.Deleted=0)) Z wyniku zapytania wiemy, że ID statusu zakończonego obiektu to 4 , stąd wartość w warunku. W warunku musieliśmy też uwzględnić przypadek, gdy nie ma relacji (RelationTableID IS NULL)
Komplet warunków, które pokażą te elementy, które są nadal przetwarzane w systemie wygląda ta:
WHERE Do.ID=@DocumentID
AND (rael.RelationTbID IS NULL OR rael.RelationTbID=50)
AND (wkfl.[Table] IS NULL OR wkfl.[Table]=’Do’)
AND ( rael.RelationTbID IS NULL OR (pardoc.PersonalNumberIS NOT NULL AND wkfl.WoSaID<>4 AND wkfl.Deleted=0))
Dwa razy używamy warunku rael.RelationTbID IS NULL jednak nie można tego uprościć.
Wynik pełnego zapytania wygląda tak:
Teraz możemy wyświetlić listę elementów, oczekujących na zakończenie przetwarzania lub, korzystając z klauzuli EXIST, sprawdzać warunki np. w procedurze obiegu.