begin
--pozn. puvodne byla proc. navrzena bez parametru FirmOID, ten se preda, pokud se emailu
--naslo ico odesilatele a tohle ico se naslo v tabulce firem
Email = Upper(Email);
--0 KOLO - proiterujeme vazby osob na firmy (sem se adresa nove pridavala), v Person_ID a Firm_ID budou jejich
--ID pokud se vyskytnou prave jednou, jinak prazdne retezce
mCount = 0;
Person_ID = '';
Firm_ID = '';
FirmOffice_ID = null;
Note = '';
for
select FP.Person_ID, FP.Parent_ID
from
SYS$EmailAddrSearch SAS
join FirmPersons FP on FP.Address_ID = SAS.Address_ID
where
SAS.Email = :Email
and (:FirmOID = '0000000000' or :FirmOID = FP.Parent_ID)
into mPerson_ID, mFirm_ID
do begin
mCount = mCount + 1;
if (mCount = 1) then begin
Person_ID = mPerson_ID;
Firm_ID = mFirm_ID;
if (Firm_ID is null) then --at to v bloku pod tim jen muzu proste porovnat
Firm_ID = '';
end else begin
if (Person_ID <> mPerson_ID) then
Person_ID = '';
if (Firm_ID <> mFirm_ID) then
Firm_ID = '';
if ((Person_ID = '') and (Firm_ID = '')) then
break;
end
end
--a vyhodnotime
if (mCount = 1) then begin
--prave jedna osoba pro jednu firmou s danou emailovou adresou
--dohledame provozovnu
select FirmOffice_ID from EmailFIND_FIRMOFFICE(:Email, :Firm_ID) into FirmOffice_ID;
suspend;
exit;
end
else begin
--1 KOLO - proiterujeme osoby s emal. adresou a jejich firmy, v Person_ID a Firm_ID budou jejich
--ID pokud se vyskytnou prave jednou, jinak prazdne retezce
mCount = 0;
Person_ID = '';
Firm_ID = '';
FirmOffice_ID = null;
Note = '';
for
select P.ID, FP.Parent_ID
from
SYS$EmailAddrSearch SAS
join Persons P on P.address_id = SAS.Address_ID
left join FirmPersons FP on FP.Person_ID = P.ID
where
SAS.Email = :Email
and (:FirmOID = '0000000000' or :FirmOID = FP.Parent_ID)
into mPerson_ID, mFirm_ID
do begin
mCount = mCount + 1;
if (mCount = 1) then begin
Person_ID = mPerson_ID;
Firm_ID = mFirm_ID;
if (Firm_ID is null) then --at to v bloku pod tim jen muzu proste porovnat
Firm_ID = '';
end else begin
if (Person_ID <> mPerson_ID) then
Person_ID = '';
if (Firm_ID <> mFirm_ID) then
Firm_ID = '';
if ((Person_ID = '') and (Firm_ID = '')) then
break;
end
end
--a vyhodnotime
if (mCount = 1) then begin
--prave jedna osoba s jednou nebo zadnou firmou
if (Firm_ID <> '') then begin --pokud ma osoba prirazenou firmu, zkusime provozovnu
select FirmOffice_ID from EmailFIND_FIRMOFFICE(:Email, :Firm_ID) into FirmOffice_ID;
end
if ((FirmOID = '0000000000') or (FirmOID = Firm_ID)) then begin
suspend;
exit;
end
end
else begin
if (Person_ID <> '') then begin --prave jedna osoba s vice firmama(zadne dalsi kroky)
if (Firm_ID = '') then
Firm_ID = null;
suspend;
exit;
end
Person_ID = null;
if (Firm_ID <> '') then begin--vice osob s jednou firmou, zkusime provozovnu
select FirmOffice_ID from EmailFIND_FIRMOFFICE(:Email, :Firm_ID) into FirmOffice_ID;
suspend;
exit;
end
--vice osob s vice firmama - zapiseme do poznamky a budem pokracovat dal
for
select P.firstname || ' ' || P.lastname || ' - ' || coalesce(F.code, '') || ' ' || coalesce(F.Name, '')
from
SYS$EmailAddrSearch SAS
join Persons P on P.address_id = SAS.Address_ID
left join FirmPersons FP on FP.Person_ID = P.ID
left join firms F on F.ID = FP.Parent_ID
where
SAS.Email = :Email
into mRow do
Note = Note || mRow || '\n'; --nevim jak zadat EOL, takze az na klientovi
end
Person_ID = null;
--2 KOLO - vyhledani v provozovnach (bud se nenasla zadna osoba s mailem, anebo se naslo vicero osob
--ve vicero firmach)
mCount = 0;
Firm_ID = '';
FirmOffice_ID = '';
for
select FO.ID, FO.Parent_ID
from
SYS$EmailAddrSearch SAS
join FirmOffices FO on FO.Address_ID = SAS.Address_ID
where
SAS.Email = :Email
and (:FirmOID = '0000000000' or :FirmOID = FO.Parent_ID)
and FO.SynchronizeAddress <> 'A'
into mFirmOffice_ID, mFirm_ID
do begin
mCount = mCount + 1;
if (mCount = 1) then begin
FirmOffice_ID = mFirmOffice_ID;
Firm_ID = mFirm_ID;
end else begin
if (FirmOffice_ID <> mFirmOffice_ID) then
FirmOffice_ID = '';
if (Firm_ID <> mFirm_ID) then
Firm_ID = '';
if ((FirmOffice_ID = '') and (Firm_ID = '')) then
break;
end
end
--a vyhodnotime
if (mCount = 1) then begin
--prave jedna provozovna
suspend;
exit;
end else begin
--tohle je oproti analyze - pokud se mail najde na vice provozovnach jedne firmy
if (Firm_ID <> '') then begin
suspend;
exit;
end
--vice provozoven na ruznych firmach - zapiseme do poznamky a budem pokracovat dal
if (Note <> '') then
Note = Note || '\n';
for
select F.code || ' ' || F.Name || ' - ' || FO.name
from
SYS$EmailAddrSearch SAS
join FirmOffices FO on FO.Address_ID = SAS.Address_ID
join firms F on F.ID = FO.Parent_ID
where
SAS.Email = :Email
and FO.SynchronizeAddress <> 'A'
into mRow
do
Note = Note || mRow || '\n'; --nevim jak zadat EOL, takze az na klientovi
end
FirmOffice_ID = null;
--3 KOLO - vyhledani v sidlech firem (nenasla se ani provozovna, anebo se jich naslo vicero)
mCount = 0;
Firm_ID = '';
for
select F.ID
from
SYS$EmailAddrSearch SAS
join Firms F on F.ResidenceAddress_ID = SAS.Address_ID
where
SAS.Email = :Email
and (:FirmOID = '0000000000' or :FirmOID = F.ID)
into mFirm_ID
do begin
mCount = mCount + 1;
if (mCount = 1) then begin
Firm_ID = mFirm_ID;
end else begin
Firm_ID = '';
break;
end
end
--a vyhodnotime
if (mCount = 1) then begin
--prave jedna firma
suspend;
exit;
end else begin
--vice firme - zapiseme do poznamky
if (Note <> '') then
Note = Note || '\n';
for
select F.code || ' ' || F.Name
from
SYS$EmailAddrSearch SAS
join Firms F on F.ResidenceAddress_ID = SAS.Address_ID
where
SAS.Email = :Email
into mRow
do
Note = Note || mRow || '\n'; --nevim jak zadat EOL, takze az na klientovi
end
if (:FirmOID <> '0000000000') then begin --dostal jsem firmu, na kterou ale nesedi dana mailova adresa, i tak ji uprednostnim
Firm_ID = :FirmOID;
end
--a suspendneme
if ((Note <> '') or (Firm_ID <> '')) then
suspend;
end
end