Structura de date

Structura de date

Dupa cum am enumerat la prezentarea tematicii, clientul acestui program a enumerat o serie de cerinte.La memoriul tehnic am justificat alegerea SQL si Delphi ca instrumente de lucru.In contiunuare voi prezenta metodele prin care am atins si rezolvat aceste cerinte avand grija totodata de pastrarea si respectarea unor reguli de baza in proiectarea bazelor de date.

Am utilizat urmatoarea structura de date: baza de date vama_granita ce contine mai multe tabele.In continuare voi explicita tabelele utilizate.

Nom_pers_fiz

Cheie compusa din:serie_pass;

Acest tabel reprezinta de fapt un nomenclator al persoanelor tranzitate intr-un sens sau altul.Utilizarea lui are avantajul economisirii de timp in momentul intrarii sau iesirii unui cetatean in tara care a mai tranzitat vama, datele lui personale fiind practic inregistrate.In plus, prin contorizarea numarului de treceri, se mai poate controla numarul de tranzitari prin vama in cazul unui embargou de exemplu.

2.Iesiri_pers

Cheie compusa din:serie_pass,nr_trecere;

In acest tabel se pastreaza o situatie precisa a persoanelor care au iesit de pe teritoriul tarii.Dupa cum se vede, se pastreaza inclusiv ora si minutul la care s-a realizat tranzitarea.Acest lucru se reflecta in posibilitatea controlului minutios al traficului.

3.Intrari_pers

Cheie compusa din:serie_pass,nr_trecere

Acest tabel este similar cu cel anterior dar tine evidenta persoanelor intrate.In plus mai beneficiaza de un camp special numit obs in care se tin informatiile de ordin special ce caracterizeaza o anumita persoana.

4.Transp_marfa_pers_fiz

Cheie compusa din:serie_pass, nr_trecere;

Acest tabel este util in retinerea si stocarea informatiilor despre marfa adusa in tara de o persoana precum si despre taxa vamala pe care trebuie sa o plateasca pe intregul transport care in mod normal este compus din mai multe produse.

5.Marfa_transp_pers_fiz

Cheie compusa din serie_pass, nr_trecere, cod_produs;

Tabela memoreaza separat pentru fiecare persoana lista detaliata cu produse importate la o anumita trecere.

6.Nomprod

Cheia compusa din cod_produs;

Tabela reprezinta un nomenclator de produse care usureaza mult munca prin concentrarea unor informatii vitale despre produse in el.

7.Trafic_auto

Cheia este compusa din: nr_auto, nr_trecere, serie_pass;

Tabelul contine o trecerile peste granita a automobilelor la data anumita, precum si persoanele care le-au condus sau au responsabilitatea lor.

8.Nom_auto_trecute

Cheia este: nr_auto;

9.Tipuri_auto

Cheia este cod_marca;

Acest tabel este un nomenclator de tipuri auto.El este foarte util in obtinerea de informatii specifice unui automobil.

10.Prod_nevamuite

Cheia este serie_pass, nr_trecere, cod_produs;

Tabela pastreaza situatia produselor depozitate de catre diferite persoane.Cheia ne permite sa identificam detaliat pe fiecare tranzitie a vamii cat a depozitat o anumita persoana.

11.Infractori

Cheia este seria pasaportului.

Acest tabel ne permite sa pastram o evidenta a persoanelor care nu au dreptul sa iasa sau sa intre in tara.Avand la dispozitie si poza, se usureaza identificarea infractorului.Acest tabel impreuna cu cel al masinilor disparute trebuie puse la dispozitie de catre departamentul de resort al M.I.

12.Incasari

Cheia este : idv, data.

Acest tabel mentine o evidenta a incasarilor tuturor vamesilor.Este utila la verificarea incasarilor pentru un anumit vames.

13.Utilizatori

Cheia Idv

Acest tabel permite setarea nivelului de acces la baza de date precum si o posibila monitorizare a vamesilor.

3.2 Solutii alese pentru rezolvarea cerintelor

Problema secutitatii este rezolvata dupa cum am spus in doua moduri.

In SQL se definesc users si fiecare este incadrat intr-un grup ce are anumite drepturi setate de catre administratorul de sistem. Acest lucru prevede incercarile de modificare a datelor din baza de date pe alte cai decat programul.

Securitatea oferita de aplicatie. Pentru fiecare utilizator s-a alocat un login si o parola care autentifica loginarea. Aceste informatii sunt pastrate intr-o tabela ,,users'', la care are acces doar administratorul aplicatiei. Acest lucru permite schimbarea parolei sau loginului. Se ofera si o protectie a usersului ordinar in fata administratorului aplicatiei, parola lui neputand fi modificata decat daca se stie vechea parola.

La intrarea in program se cere loginul si parola userului. Fara aceste informatii nu se permite accesul in program. La introducerea loginului se actioneaza o procedura stocata de cautare care certifica daca loginul este bun sau nu.Acesta este codul procedurii care realizeaza verificarea:

CREATE    PROCEDURE cauta_user

(@loghin char(20), @parola char(10),

@ret int output, @nivel int output, @idv int output)

AS

If exists(select * from utilizatori where loghin=@loghin and parola=@parola)

begin

set @ret=1

set @nivel=(select nivel from utilizatori where loghin=@loghin and parola=@parola)

set @idv=(select idv from utilizatori where loghin=@loghin and parola=@parola)

end

else

begin

set @ret=0

set @nivel=0

set @idv=0

end

Procedura primeste la intrare ca parametrii loginul si parola userului. Daca acestea nu exista sau sunt introduse incorect valoarea @ret va fi setata la 0. Aceasta valoare returnata este testata in program avand ca efect permiterea sau nu a accesului la aplicatie.

Odata intrat in program un user obisnuit va acces la operatii obisnuite ca si marcari intrare sau iesiri persoane, incasari, control masini intr-un cuvant tot ceea ce implica munca unui vames.

In cazul marcarii intrarii unei persoane, avem doua cazuri: persoana a mai trecut vama, sau este pentru prima data cand trece. In al doilea caz este necesar o introducere a datelor personale. Cautarea unei persoane se face dupa seria pasaportului.Odata introdusa seria pasaportului vom vedea daca persoana a mai trecut sau nu si deci in consecinta daca i se vor mai lua datele personale.Procedura de cautare dupa seria pasaportului este utilizata in mai multa locuri in cadrul programului si are urmatoarea structura:

CREATE PROCEDURE cauta_serie_pass @ser char (15),@out int output AS

if exists(select * from nom_pers_fiz where serie_pass=@ser)

set @out=1

else

set @out=0

Variabila @out este setata la 1 daca s-a gasit persoana. Aceasta cautare se face in nomenclatorul de persone trecute(Nom_pers_fiz).Fiecare persoana are atasat un contor reprezentand numarul de treceri alpersoanei respective. Acesta procedura cauta maximul din tabelele de intrari si iesiri pe care-l incrementeaza cu 1.Aceasta procedura are urmatoarea structura:

CREATE PROCEDURE max_la_trecere @s char(15),@o int output AS

/* exista si in intr si in iesiri*/

if exists (select * from intrari_pers i, iesiri_perse, nom_pers_fiz n

WHERE

(i.serie_pass=@s) and (e.serie_pass=@s) and (n.serie_pass=@s))

/*daca numarul de treceri din intrari este mai mare ca si cea din iesiri*/   

if(select max(nr_trecere) from intrari_pers where serie_pass=@s)>

(select max(nr_trecere) from iesiri_pers where serie_pass=@s)

set @o=1+(select max(nr_trecere) from intrari_pers where serie_pass=@s)

else

set @o=1+(select max(nr_trecere) from iesiri_pers where serie_pass=@s)

else

/*este numai in iesiri*/

if exists (select * from iesiri_pers e,nom_pers_fiz n where

(e.serie_pass=@s) and

(n.serie_pass=@s) )

set @o=1+(select max(nr_trecere) from iesiri_pers where serie_pass=@s)

else

/* este in intrari*/

if exists (select * from intrari_pers e,nom_pers_fiz n where

(e.serie_pass=@s) and

(n.serie_pass=@s) )

set @o=1+(select max(nr_trecere) from intrari_pers where serie_pass=@s)

else

set @o=1

In variabila @o de iesire vom avea numarul de treceri corespunzator. Odata completate datele despre o persoana se va trece daca este cazul la completarea datelor privind marfa transportata in cazul intrarilor. Aceasta optiune se gaseste pe forma de monitorizare persoane intrate in tara.

La selectarea acestei optiuni se deschide o forma complexa pentru completarea marfurilor transportate de o persoana. Aceasta forma are urmatoarea structura:

Aceasta forma permite calculul automat al taxelor pe masura completarii datelor privind marfa transportata si cea a cantitatii. Se afiseaza atat valoarea partiala a taxei pe produs cat si valoarea totala acumulata. Aceste calcule se realizeaza in SQL prin doua proceduri care au ca parametrii de intrare codul produsului importat si cantitatea. Codul produsului importat este returnat functie de denumirea lui de catre procedura SQL returneaza_cod_fctie_de_produs

CREATE PROCEDURE returneaza_cod_fctie_de_produs

@pr char(20),

@o int output

AS

set @o=(select cod_produs from nomprod where denumire =ltrim(rtrim(@pr)))

Se apeleaza functiile ltrim si rtrim care elimina spatiile goale dintr-un sir de caractere. Acest lucru permite determinarea corecta a codului la introducerea accidentala de spatii in denumirea produsului.

Procedurile care calculeaza taxele si pretul pentru un produs si o cantitate preiau codul produsului returnat de procedura anterioara precum si valoarea din casuta de dialog corespunzatoare cantitatii si ne returneaza taxa si valoarea.Aceste proceduri sunt:

CREATE PROCEDURE taxa_per_produs @cod int,@val int,@taxa int output

AS

set @taxa=@val*(select taxa_per_um from nomprod where cod_produs=@cod)/100

Aceasta procedura este relativ simpla. Am utilizat evenimentul OnExit() pentru casuta de dialog ce contine cantitatea pentru a apela aceste proceduri.Exemplific in continuare aceste apeluri:

/*se apeleaza procedura de returnare a codului produsului functie de denumirea lui*/

return_cod.Params[1].AsString:=denumire.Text;

return_cod.prepare;

return_cod.ExecProc;

cod:=return_cod.Params[2].AsInteger;/*se preia codul returnat de procedura*/

/*se calculeaza valoarea produselor*/

return_valoare_produse.params[1].AsInteger:=cod;

return_valoare_produse.params[2].AsInteger:=StrToInt(cantitate.Text);

return_valoare_produse.Prepare;

return_valoare_produse.ExecProc;

ptr_valoare:=return_valoare_produse.params[3].AsInteger;

/*se afiseaza vaoarea*/

valoare.Text:=IntToStr(return_valoare_produse.params[3].AsInteger);

/*se calculeaza taxa*/

ret_taxa.Params[1].AsInteger:=cod;

ret_taxa.Params[2].AsInteger:=ptr_valoare;

ret_taxa.prepare;

ret_taxa.execProc ;

/*se returneaza taxa si se afiseaza*/

ptrtaxafinala:=ret_taxa.Params[3].AsInteger;

taxa.text:=IntToStr(ret_taxa.Params[3].AsInteger);

/*se calculeaza si afiseaza in paralel valorile taxei totale*/

vtotal:=Vtotal+ptr_valoare;

ttotal:=ttotal+ptrtaxafinala;

val_totala.Text:=IntToStr(vtotal);

taxa_totala.Text:=IntToStr(ttotal);

O alta facilitate a acestei forme o reprezinta lista totala a produselor care este de tip drop down.Acest lucru inseamna ca la tastarea unei litere se face o selectie a tuturor produselor, ordonat alfabetic si in lista vor ramane doar acele produse a caror denumire au la inceput literele tastate. De exemplu la tastarea primei litere 'a' vor ramane doar produsele care incep cu 'a'.Daca se mai tasteaza o alta litera 'f' de exemplu vor ramane doar acele produse a caror denumire incepe cu 'af'. Acest lucru reprezinta o facilitate care usureaza mult munca vamesilor crescand in viteza aplicatia.

Acest tip de lista l-am utilizat foarte mult in aplicatia mea, mai peste tot unde am de introdus o valoare care trebuie selectata dintr-o lista.Lucrul acesta este realizat prin apelul procedurii SQL selectie_ptr_produse apelata la fiecare introducere a unui nou caracter in casuta de dialog pentru denumire. Eventul care trateaza aceasta introducere de caractere este OnChange().

Procedura selectie_ptr_produse returneaza o lista a produselor care incep cu un anumit grup de litere care se dau ca parametru de intrare:

CREATE PROCEDURE selectie_ptr_produse @p char(20)

AS

select denumire from nomprod where denumire like rtrim(ltrim(@p))order by denumire

Eventul On Change se activeaza la fiecare introducere de caracter si ca urmare si procedura de selectie pentru produse se va apela simultan cu introducerea unor noi caractere dar de fiecare data cu alti parametri. Rezultatul selectiei este legat printr-un DataSource la un DBGrid in care rezultatul selectiei va fi in permanenta reactualizat cu valorile corespunzatoare.

/*se inchide procedura pentru a se asigura refreshul*/

/*fara acest apel rezultatele selectiei nu se vor vedea */

selectie_produs.Close;

/*selectia se face dupa grupul de litere care se afla

introdus in casuta de dialog cu denumirea*/

selectie_produs.Params[1].AsString:=denumire.Text+'%';

selectie_produs.prepare;

/*se afiseaza rezultatele selectiei*/

selectie_produs.Open;

In cazul in care persoana care tranziteaza se hotaraste sa renunte dintr-un motiv sau altul la un anume produs, se apeleaza o procedura de stergere, produsul ales pentru stergere fiind selectat direct din lista cu produse importate de persoana. Apelul acestei proceduri se face la apasarea unui butoncu CAPTION-ul Stergere.Eventul la care este legat apelul acestei proceduri este OnButtonClick(). Apelul necesita niste pregatiri speciale precum si un refresh al listei dupa executarea listei:

retv:=afisare_produse.Fields[2].value;

rett:=afisare_produse.Fields[3].value;

/*parametrul de intrare este luat din lista de produse */

return_cod.Params[1].AsString:=afisare_produse.Fields[0] .value;

/*s calculeaza codul produsului functie de denumire*/

return_cod.prepare;

return_cod.ExecProc;

cod:=return_cod.Params[2].AsInteger;

/*se apleleaza procedura de stergere din SQL avand ca parametrii de intrare seria de pasaport, numarul trecerii si codul produsului sters*/

del.params[1].AsString:=serie_pass.text;

del.params[2].AsInteger:=StrToInt(nr_trecere.text);

del.params[3].AsInteger:=cod;

del.prepare;

del.execproc;

afisare_produse.Params[1].AsString:=serie_pass.Text;

afisare_produse.Params[2].AsInteger:=StrToInt(nr_trecere .Text);

afisare_produse.close;

afisare_produse.prepare;

afisare_produse.open;

Procedura SQL de stergere are urmatoarea forma:

CREATE PROCEDURE     [delete_marfa_transp_pers_fiz_si_cu_cod]

(@serie_pass_1 char(15),

@nr_trecere_2 int,

@cod_produs_3 int)

AS DELETE [vama_granita].[dbo].[marfa_transp_pers_fiz]

WHERE

( [serie_pass] = @serie_pass_1 AND

[nr_trecere] = @nr_trecere_2 AND

[cod_produs] = @cod_produs_3)

Aceasta procedura este creata in parte cu ajutorul wizard-ului un instrument pus la dispozitie de SQL pentru creare automata a diferite componente ca proceduri stocate, tabele, etc.

Odata introduse aceste informatii, se afiseaza totalul taxelor vamale.In continuare se ofera posibilitatea depozitarii de marfa in cazul in care nu se dispune de bani necesari pentru plata taxei se ofera posibilitatea depozitarii de marfa.Daca se alege acesta optiune, va aparea urmatorul ecran:

Se ofera o faciliate speciala pentru grabirea formalitatilor de depozitare a marfii.La intrarea in forma se cere suma de care dispune persoana pentru un calcul mai eficient al marfii care trebuie sa fie depozitata. In permanenta se realizeaza un calcul pentru a se stabili daca mai este nevoie sa se depoziteze marfa sau persoana are banii necesari.

Bineinteles exista posibilitatea de a se scoate marfurile depozitate. Acest lucru se realizeaza prin selectarea unei persoane si afisarea marfii care a fost depozitata. La selectarea persoanei se afiseaza suma care trebuie platita.

La login-are fiecare vames este inregistrat in baza e date incasari.In tabela este pastrata inclusiv ora la care s-a loginat si ora al care a iesit din program.Acest lucru este realizat prin niste etichete in-out. In tot acest interval sunt marcate toate incasarile efectuate. Un vames poate incasa fie la percepera unei taxe fie la scoaterea unei marfi depozitate de unul dintre persoanele ce au tranzitat. Deci apelul procedurii de incasare se face in doua momente: la apasarea butonului ,,Depozitare,, si la confirmarea platii taxelor vamale pentru produsele depozitate.Apelul este de forma:

/* primul parametru este identificatorul vamesului declarat ca public in formul principal, al doilea fiind suma*/

incasari.Params[1].AsInteger:=form1.idv;

incasari.Params[2].AsInteger:=StrTOint(sreala.text);

incasari.Prepare;

incasari.ExecProc;

Procedura SQL apelata are mai multi parametrii, dar acestia sunt completati automat prin interogarea sistemului.Procedura este de forma:

CREATE PROCEDURE insert_incasari @idv int,@suma int AS

/*incepere tranzactie*/

begin tran

declare @min int

declare @ora int

declare @ziua int

declare @luna int

declare @anul int

/*se preia data de la sistem*/

set @ziua=day(getdate())

set @luna=month(getdate())

set @anul=year(getdate())

set @ora=datepart(hh,getdate())

set @min=datepart(mi,getdate())

insert into incasari values(@idv, @min,@ora,@ziua,@luna,    @anul,@suma,null)

commit

S-au apelat functii de preluare a datei getdate() specificandu-se initial ce componenta se doreste a se prelua day, month, etc.

O cerinta a programului a fost posibilitatea regasirii unor persoane avand cat mai putine informatii despre aceasta persoana.Se ofera spre completare date despre seria de pasaport, nume, prenume, cetatenie.Programul va returna lista tuturor persoanelor care indeplinesc minim aceste conditii(contin in grupul de litere scris in caracteristica corespunzatoare).Daca un criteriu de selectie este lasat gol atunci se vor returna toate posibilitatile tinand cont doar de celelalte criterii de selectie.Selectul din procedura SQL care rezolva aceasta problema este de forma:

select distinct i.serie_pass Serie_pasaport ,n.nume Nume,n.Prenume Prenume,

n.cetatenie Cetatenie,n.zi ,n.luna,n.an

from intrari_pers i, nom_pers_fiz n where

(i.serie_pass like ('%'+@s+'%'))and

(i.serie_pass =n.serie_pass) and

(n.nume like('%'+@n+'%') ) and

(n.prenume like('%'+@p+'%')) and

(n.cetatenie like('%'+@c+'%'))

Comparatorul LIKE combinat cu optiunea '%' ne returneaza toate posibilitatile care incep cu ceea ce se compara.Un exemplu edificator:

select * from tabela_exemplu where nume like'ac%' va returna toate numele care incep cu 'ac'.

In program am introdus o serie de validari la nivelul clientului.Aceste validari se fac pentru a nu se introduce tipuri de date necorespunzatoare. Validarile se fac pe tratarea evenimentului OnExit() al casutelor de dialog. Exemplific cu o validare pentru data.Se va afisa mesaj de eroare la introducerea unei date care nu exista sau a unui caracter:

tmp:=strtointdef(m_ziua.text,0);

if (tmp<1) or (tmp>31) then

begin

showmessage('Data incorecta !');

activecontrol:=m_ziua;

end;

Aceste validari executate la client ar putea fi la fel de bine executate si pe server, dar avand in vedere faptul ca este o aplicatie client-server acest lucru presupune o incarcare a retelei si din considerente de efcienta se vor face validarile de acest tip pe client.

La unele dintre proceduri este nevoie sa setam nivelul de acces.De exemplu la procedura de introducere a unui produs la depozitare, daca in acest timp cineva va sterge produsul respectiv din baza de date,va aparea inconsistenta datelor. De aceea se va bloca in nomenclatorul de produse produsul respectiv pentru stergere.Se reconanda utilizarea comenzii ,,serializable".