if exists (select * from SYSTRIGGERS where LCase(trigname)=lcase('TrigGuestsDates'))
  then
  drop trigger TrigGuestsDates
end if

GO

if exists (select * from SYSTRIGGERS where LCase(trigname)=lcase('TrigTogetherUpd'))
  then
  drop trigger TrigTogetherUpd
end if

GO

if (select count(*) from sys.syscolumns where tname='Guests' and cname='IsEgnEnch')=0
  then
    alter table Guests add IsEgnEnch integer default 0;
end if

GO

IF (EXISTS (select proc_name from sysprocedure where proc_name='IsDigit')) THEN
  Drop function IsDigit;
END IF;

GO

create function "DBA".IsDigit(in @dd varchar(1))
returns integer
begin
  declare @nn integer;
  set @nn=ascii(@dd);
  if @nn>47 and @nn<58 then
    return(1)
  else
    return(0)
  end if
end
//[v 14.03.2006] Created by Jorko

GO

IF (EXISTS (select proc_name from sysprocedure where proc_name='CheckDate')) THEN
  Drop function CheckDate;
END IF;

GO

CREATE FUNCTION CheckDate(CurrDate varchar(8))
RETURNS integer
// CurrDate with format yymmdd
ON EXCEPTION RESUME

BEGIN
    DECLARE DateValue Date;
    DECLARE InvalidDate
    EXCEPTION FOR SQLSTATE '53018';
    set DateValue= (select date(CurrDate)) ;
    IF SQLSTATE=InvalidDate then
           RETURN(0);
	else
           RETURN(1);
	end if;

END
// [16.03.2006] Created by Ivaylo

GO

IF (EXISTS (select proc_name from sysprocedure where proc_name='CheckEGN')) THEN
  Drop function CheckEGN;
END IF;

GO

create function "DBA"."CheckEGN"(in @EGNStr varchar(18))
  returns integer
begin

declare Result integer;
declare @DayEGN varchar(2);
declare @MonthEGN varchar(2);
declare @YearEGN varchar(2);
declare I Integer;
declare IntFlag Integer;
declare cSum Integer;
declare dd integer;
declare mm integer;
declare mes integer;
declare yy integer;
declare dd_str varchar(10);
declare mm_str varchar(10);
declare yy_str varchar(10);
if @EGNStr = ''
  then
    Return(0);
End if;
if Length(@EGNStr) <> 10
  then
    Return(0);
End if;
set i=1;
set IntFlag=1;
while i<11 loop
  if IsDigit(substr(@EGNStr,i,1))=0
    then
      set IntFlag=0;
  end if;
  set i=i+1;
end loop;
if IntFlag=0
  then
    Return(0);
end if;
set @YearEGN=substr(@EGNStr,1,2);
set @MonthEGN=substr(@EGNStr,3,2);
set @DayEGN=substr(@EGNStr,5,2);
set yy_str=@YearEGN;
set mm_str=@MonthEGN;
set dd_str=@DayEGN;
if length(dd_str)=1
  then
    set dd_str='0'+dd_str;
end if;
set mm=mm_str;
set yy=@YearEGN;
set yy_str=@YearEGN;
if ((yy>=0) and (mm>40))
  then
    set yy_str='20'||yy_str;
  else
    set yy_str='19'||yy_str;
end if;
if mm>40
  then
    set mes=mm-40;
  else
    set mes=mm;
end if;
if mes<10
  then
    set mm_str='0'||mes;
  else
    set mm_str=mes;
end if;
if length(mm_str)=1
  then
    set mm_str='0'||mm_str;
end if;
//message 'yy_str='||yy_str;
//message 'mm_str='||mm_str;
//message 'dd_str='||dd_str;
message yy_str||mm_str||dd_str;

if CheckDate(yy_str||mm_str||dd_str)=0
  then
    Return(0);
end if;
set cSum=substr(@EGNStr,1,1)*2+substr(@EGNStr,2,1)*4+substr(@EGNStr,3,1)*8;
set cSum=cSum+substr(@EGNStr,4,1)*5+substr(@EGNStr,5,1)*10+substr(@EGNStr,6,1)*9;
set cSum=cSum+substr(@EGNStr,7,1)*7+substr(@EGNStr,8,1)*3+substr(@EGNStr,9,1)*6;
set cSum=mod(mod(cSum,11),10);
if cSum<>substr(@EGNStr,10,1)
  then
    return(0);
  else
    return(1);
end if;
end

GO

update Guests set IsEgnEnch=CheckEGN(Guests.EGN)

GO

CREATE TRIGGER "TrigGuestsDates" after insert,update order 1
on "DBA".Guests
referencing new as GSNEW
for each row
begin
declare @PPDate date;
declare @BDate date;
//message '-- TrigGuestsDates...';
set @PPDate=FloatToDate(GSNEW.PassportDate);
if CheckEGN(GSNEW.EGN)=1
then
set @BDate=if GSNEW.EGN is not null and Length(GSNEW.EGN)>5 and GSNEW.HumanKind=1 then EGNToDate(GSNEW.EGN)
else FloatToDate(GSNEW.BirthDate)
endif;
  update Guests set IsEgnEnch=1 where GuestNum=GSNEW.GuestNum;
else
  update Guests set IsEgnEnch=0 where GuestNum=GSNEW.GuestNum;
end if;
if GSNEW.PassportDateDt is null or GSNEW.BirthDateDt is null
or @PPDate<>GSNEW.PassportDateDt or @BDate<>GSNEW.BirthDateDt then
update Guests set BirthDateDt=@BDate,BirthDate=DateToFloat(@BDate),
PassportDateDt=@PPDate
where GuestNum=GSNEW.GuestNum
end if
/*  if GSNEW.HumanKind=1 and GSNEW.NativeCountry<>0 then
update Guests set NativeCountry=0 where GuestNum=GSNEW.GuestNum
end if
;
if GSNEW.HumanKind<>1 and GSNEW.NativeCountry=0 then
update Guests set NativeCountry=-1 where GuestNum=GSNEW.GuestNum
end if
*/
end
//[v 01.06.18]
//[v 21.03.2006] Jorko with CheckEGN

GO

Create trigger
TrigTogetherUpd after update order 2 on "DBA".Guests
referencing old as GSOLD new as GSNEW
for each row
begin
  //message '-- TrigTogetherUpd...';
  //  message '-- TrigTogetherUpd...';
  if(GSOLD.ReservePlanNo<>GSNEW.ReservePlanNo)
    and(GSNEW.TogetherWith<>0) then
    update Guests set ReservePlanNo=GSNEW.ReservePlanNo
      where TogetherWith=GSNEW.TogetherWith and Status=0
  end if
  ;
  if(GSNEW.Reservation<>0) and(GSNEW.Status=0) then
    call CalcBoardPlanRsr(GSNEW.GuestNum)
  end if
end
//[v 01.05.16]