[SOLVED] Poprawność, walidacja numeru pesel Oo Base

[mdpiotr]

Szanowni.
Baza danych Postgresql, jako "nakładka graficzna" zbiór kwerend, formularzy, raportów, makr jak do tej pory MS Access. Obecnie próbuję zamienić w/w MS Access na Openoffice Base i wszystko, no prawie wszystko, działa z wyjątkiem walidacji nr pesel. Walidacja nr pesel założona była w MS Access na polu tekstowym, z którego funkcja, za pomocą makra, pobierała wprowadzane do pola dane uniemożliwiając zapisanie rekordu w przypadku błędnego nr pesel. W załączniku podaję kod marka i funkcji, proszę o pomoc w przystosowaniu kodu do współpracy z Openoffice Base. Z góry dziękuję.

[Jermor]

W swoim opracowaniu poświęconym funkcjom w programie Calc, jako przykładu użyłem utworzenie funkcji sprawdzającej poprawność numeru PESEL. Funkcję znajdziesz tu: https://yestok.pl/lbo/y61.php. Możesz zmodyfikować tę funkcję do własnych potrzeb. W szczególności przypisać funkcji typ Boolean, w ostatnim podstawieniu zamiast:

Kod: Zaznacz cały

PaniPan=Iif(CInt(Left(Right(pesel,2),1)) MOD 2,"Pan","Pani")

wpisać

Kod: Zaznacz cały

PaniPan=True

a we wszystkich innych sekwencjach

Kod: Zaznacz cały

PaniPan=

wpisać

Kod: Zaznacz cały

PaniPan=False

[Rafkus]

Podana przez Ciebie funkcja jest z grubsza poprawna - niewiele zmian trzeba było dokonać. Ale jest ona nie dokończona, brak w niej sprawdzenia poprawności daty, np: dla peslu 44444444444 - suma kontrolna wychodzi poprawna ale funkcja generuje błąd gdyż próbuje stworzyć datę dla roku 2044 miesiąca 24 i dnia 44.

Drugim problemem jest powiązanie tej funkcji z jakimś polem formularza. Do tego celu wymyśliłem takie makro i proponuję przypisać je do pola wzorca (do którego będzie wpisywany pesel) i przypisać je do zdarzenia: "Po utracie fokusa".

Kod: Zaznacz cały

Sub Pole_Pesel
  Dim oDoc As Variant
  Dim oForms As Variant
  Dim oObj1,oObj2  As Variant
  Dim pesel As String

	oDoc = ThisComponent.getDrawPage()		'w bieżącym dokumencie są formatki formularza 
	oForms = oDoc.Forms(0)					' jest Główny Zbiór Danych (GWD)
	oObj1 = oForms.getByName("txtPesel")	'w GWD jest pole o nazwie txtPesel
	pesel = oObj1.Text						'w polu txtPesel jest wpisany tekst
	oObj2 = oForms.getByName("ePesel")		'w GWD jest pole etykiety o nazwie jest ePesel	 

	Sprawdzenie = Sprawdz_Pesel(pesel, 1)
 	If Sprawdzenie = True  Then
 		wynik = "Płeć danej osoby to: " & iif(Sprawdz_Pesel(pesel, 2)="K", "Kobieta", "Mężczyzna") & chr(10)
 		wynik = wynik & "Data urodzenia: " & Sprawdz_Pesel(pesel, 3)
		oObj2.label = wynik					'w polu etykiety ePesel wpisz wynik
 	Else
		oObj2.label = "Pesel błędny lub niepodany"		'w polu etykiety ePesel wpisz wynik
	End If	
End Sub

Zastosowane pole wzorca pozwala na wpisanie tylko 11 cyfr, liter nie dopuszcza. Po wpisaniu do niego jakieś liczby należy wcisnąć enter, tab, lub wybrać myszką jakiś inny element aby uruchomić makro.
W załączeniu plik z prezentowanym rozwiązaniem.

[Rafkus]

Dodałem do makra kontrolę daty.

Edytowano1:
Dodałem do Tabeli1 pola "DataUrodzin" oraz "Plec", oraz umieściłem je w formularzu. Zmodyfikowałem makro PolePesel tak, aby to ono wpisywało poprawne wartości do tych dwóch pól.

Edytowano2:
Poprawiłem nieco kod makra, do obliczeń roku, miesiąca i dnia wykorzystałem formuły zastosowane przez @Jermora

Edytowano3:
Jeśli ktoś chce, to dodałem w pliku kontrolę nr Pesla przy pomocy kwerend, bez użycia makra.
Założenie jest takie, że użytkownik będzie wprowadzał dane do tabeli przy pomocy formularza. Pole wzorca nie pozwoli na wpisane znaków innych niż jedenastu cyfr, a zastosowana maska znaków będzie pokazywała znak "_" jeśli tych cyfr będzie za mało. W przypadku zapisania rekordu z mniejszą ilością cyfr kwerendy "ogłupieją" - nie będą potrafiły wykonać jakiejś operacji liczbowej na znaku "_". Można by zrobić dodatkowe zabezpieczenie wykrywające taki znak, ale nie chciałem bardziej komplikować kodu kwerend. Pod tym względem uważam, że makro daje lepszą kontrolę nad wynikiem.
Dodatkowe zabezpieczenie widziałbym w np. w ten sposób, że wszystkie obliczane wartości rok, miesiąc, dzień, płeć i suma kontrolna z kwerendy OdczytajPesel wsadziłbym do takiego wyrażenia JEŻELI:

Kod: Zaznacz cały

 casewhen (LOCATE ('_',"Pesel")>0 , '*', wyrażenie) as "NazwaPola"

UWAGA: W przypadku pola SumaKontrolna nie można użyć gwiazdki - to musi być jakaś liczba, proponuję -1
Funkcja LOCATE mówi na której pozycji w peselu jest jakiś znak (tutaj jest nim '_'), jeśli go niema to zwróci 0. Jeżeli wartość otrzymaną z LOCATE będzie >0 to w polu kwerendy pojawi się znak "*", w przeciwnym razie pojawi się wartość z wyrażenia.
Następnie w kwerendzie SprawdźPesel (SprawdźPesel2) do pola Poprawność również trzeba będzie dopisać na początku jeszcze jeden warunek :

Kod: Zaznacz cały

CASE WHEN LOCATE ('_',"Pesel")>0  THEN FALSE 
	WHEN CAST( "miesiac" AS INTEGER ) NOT BETWEEN 1 AND 12 THEN FALSE 
	WHEN CAST( "dzien" AS INTEGER ) NOT BETWEEN 1 AND 31 THEN FALSE 
	WHEN "dzien" = '31' AND "miesiac" IN ( '02','04', '06', '09', '11' ) THEN FALSE 
	WHEN "miesiac" = '02' and "dzien" = '30' THEN  FALSE 
	WHEN not( ( MOD( "rok", 4 ) = 0 AND MOD( "rok", 100 ) = 0 ) OR MOD( "rok", 400 ) = 0 ) AND "dzien" = '29'and "miesiac" = '02' THEN FALSE 
	WHEN MOD( "SumaKontrolna", 10 ) = 0 AND SUBSTR( "Pesel", 11, 1 ) = 0 THEN TRUE 
	WHEN 10 - MOD( "SumaKontrolna", 10 ) = SUBSTR( "Pesel", 11, 1 ) THEN TRUE 
	ELSE FALSE END AS "Poprawnosc"

Edytowano4:
Nowa wersja, Kwerenda OdzczytajPesel_0 jest niepotrzebna zostawiłem ją aby pokazać "głupawkę", kwerendy SprawdźPesel i SprawdźPesel2 są równoznaczne ze sobą - różnią się tylko źródłem danych. SprawdźPesel nie potrzebuje do działania kwerendy OdzczytajPesel (jej zapytanie jest zdefiniowane jako źródło).
W makrze PolePesel dodałem możliwość wyczyszczenia pola daty.
Edytowano5:
Kwerenda SprawdzPesel oblicza dodatkowo wiek osoby.
Poprawiłem makro - poprzednie zgłaszało błąd podczas zapisywania nowego rekordu

[Jermor]

Kolego @Rafkus
Ja datę otrzymaną w postaci tekstu "RRRR-MM-DD" otrzymuję taką sekwencją (zakładając, że pesel jest na pewno 11-to cyfrowym ciągiem)

Kod: Zaznacz cały

i=Int(CInt(Mid(pesel,3,1))/2)
lata=Array(19,20,21,22,18)
r=lata(i)&Left(pesel,2)&"-"&Format((CInt(Mid(pesel,3,2))-i*20),"00")_
		&"-"&Mid(pesel,5,2)
On Local Error GoTo niedata
i=DateValue(r)

Ostatnia instrukcja z funkcją DateValue() wygeneruje bład jeżeli wyznaczony ciąg nie jest prawidłową datą. I tylko taką rolę może tu odegrać.
Formalnie można zastosować formułę:

Kod: Zaznacz cały

DateDiff("yyyy",DateValue(r),Date)

Poda ona różnicę w latach między datą bieżącą a datą wynikającą z peselu. Jeśli różnica będzie <0, to oznaczałoby datę osoby jeszcze nienarodzonej. Wartość 0 oznacza pesel osoby z bieżącego roku, zaś liczba >0 oznacza różnicę lat. Można to wykorzystać do zweryfikowania czy dana osoba może być uwzględniona w bazie, np. czy jest pełnoletnia (>=18). No i oczywiście skok do obsługi błędu, gdy data z peselu nie jest poprawną datą.
Nawiasem pisząc twoja data 444444 podana w przykładzie rzeczywiście dotyczy dnia 44-tego, ale kwietnia roku 2144.

[Rafkus]

Kolego @Jermor
Czytałem twój artykuł na yestoku, ale po pierwsze chciałem spróbować zrobić to nieco inaczej bo po drugie obsługę błędów stosuję w ostateczności. Traktuję jako koło ratunkowe a nie jako sposób bezpiecznego opuszczenia okrętu po wpłynięciu na rafę - a zwłaszcza gdy wiedziało się o jej istnieniu. W tym zagadnieniu kontrola daty jest w miarę prosta do zrealizowania: obliczony miesiąc musi być z przedziału 01÷12, oraz dzień z przedziału od 01 do ostatniego dnia danego miesiąca.

Pisząc o dacie z pesla 444444... nawiązywałem do sytuacji powstałej w przypadku makra dostarczonego przez @mdpiotr, a konkretnie do tego fragmentu:

Kod: Zaznacz cały

ElseIf Opcja = 3 Then ' ma zwrócić datę urodzenia
If Val(D3 & D4) < 13 Then   '44<13 <--- false, wykona polecenie po else
Sprawdz_Pesel = DateSerial(Val("19" & D1 & D2), Val(D3 & D4),Val(D5 & D6))
Else   
Sprawdz_Pesel = DateSerial(Val("20" & D1 & D2), Val(D3 & D4) - 20,Val(D5 & D6))   '<=== DateSerial(2044,  24,  44)
End If

Czyli jak pisałem wcześniej

jest z grubsza poprawna (...)Ale jest ona nie dokończona,

i czego nie napisałem: nie uwzględniała innych stuleci.

[mdpiotr]

Na wstępie, chciałem podziękować za zainteresowanie tematem. Korzystając z kodu zamieszczonego w pesel.odb przez Sz. Kolegę @Rafkus i po całym dniu pracy dotarłem szczęśliwie do końca, przystosowując Oo Base do pracy z BD Postrgesql nie ograniczając przy tym funkcji użytkowych jakie miałem w MS Access. Analizując przywołany wyżej kod jak również kod z załącznika https://yestok.pl/lbo/y61.php. Sz. Kolegi @Jermor, "wiem że nic nie wiem" i czeka mnie jeszcze dużo nauki. Jeszcze raz dziękuję za zainteresowanie i szybko udzieloną fachową pomoc. Sprawa rozwiązana.