IF not (EXISTS(select * from sys.systable as T where LCase(T.table_name) = LCase('ImportGuests'))) THEN
	create table ImportGuests (
	"N" integer not null default autoincrement,
	"D0" varchar(250),
	"D1" varchar(250),
	"D2" varchar(250),
	"D3" varchar(250),
	"D4" varchar(250),
	"D5" varchar(250),
	"D6" varchar(250),
	"D7" varchar(250),
	"D8" varchar(250),
	"D9" varchar(250),
	"D10" varchar(250),
	"D11" varchar(250),
	"D12" varchar(250),
	"D13" varchar(250),
	"D14" varchar(250),
	"D15" varchar(250),
	"D16" varchar(250),
	"D17" varchar(250),
	"D18" varchar(250),
	"D19" varchar(250),
	"D20" varchar(250),
	"D21" varchar(250),
	"D22" varchar(250),
	"D23" varchar(250),
	"D24" varchar(250),
	"D25" varchar(250),
	"D26" varchar(250),
	"D27" varchar(250),
	"D28" varchar(250),
	"D29" varchar(250),
	"D30" varchar(250),
	"D31" varchar(250),
	"D32" varchar(250),
	"D33" varchar(250),
	"D34" varchar(250),
	"D35" varchar(250),
	"D36" varchar(250),
	"D37" varchar(250),
	"D38" varchar(250),
	"D39" varchar(250),
	"D40" varchar(250),
	"D41" varchar(250),
	"D42" varchar(250),
	"D43" varchar(250),
	"D44" varchar(250),
	"D45" varchar(250),
	"D46" varchar(250),
	"D47" long varchar,
	"Session" integer,
	primary key ("N")
)

end if;

GO

IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('ImportGuests'))) THEN 
	Drop procedure ImportGuests
END IF

GO

create procedure DBA.ImportGuests( in SessionId integer, in PrinterName varchar(50), in WorkPlaceRegId varchar(100), in UserNo integer ) 
begin atomic
  declare @GuestNum integer;
  declare @CategoryN integer;
  declare @HumanKind integer;
  declare @IsEgn varchar(5);
  declare @OblastN integer;
  declare @ObshtinaN integer;
  declare @SettlementN integer;
  declare @Sex varchar(5);
  declare @KursN integer;
  declare @SpecNo integer;
  declare @UniN integer;
  declare @PassportValDt date;
  declare @PassportDateFloat float;
  declare @NumLodg integer;
  declare @FromDateInt integer;
  declare @ToDateInt integer;
  declare @NumDays integer;
  declare @RoomNum integer;
  declare @PriceKindN integer;
  declare @RoomPrice numeric(12, 2);
  declare @RoomClass integer;
  declare @KodCurrency integer;
  declare @DocUNP varchar(50);
  declare @PrinterN integer;
  declare @SaleOrdersId integer;
  declare @HotelId integer;
  declare @HotelName varchar(100);
  declare @SpecType integer;
  declare @ZapovedDT Date;
  declare @errorStr long varchar;
  declare @loginDT varchar(20);
  declare @loginDTInt integer;
  declare @IsRoomFull integer;
  
  set bvrCountImportedGuests=0;
  set @loginDT=(select first DateFormat(InsDateTime, 'DD.MM.YYYY') from HotelActionsLog order by InsDateTime desc);
  set @loginDTInt=DATEDIFF(DAY, '1899-12-30', CONVERT(DATE, REPLACE(@loginDT, ' г.', ''), 104));

  for f as curs scroll cursor for
    select N as @ImpN, 
	  D0 as @Category,
	  D1 as @StudentName,
	  D2 as @StudentNameLat,
	  D3 as @Nationality,
      D4 as @EGN,
	  D5 as @Adres,
	  D6 as @Oblast,
	  D7 as @Obshtina,
	  D8 as @Selishte,
	  D9 as @PassportNum,
      D10 as @PassportDate,
	  D11 as @PassportWhere,
	  D12 as @PassportVal,
	  D13 as @Gender,
      D14 as @University,
	  D15 as @Kurs,
	  D16 as @FakNum,
	  D17 as @OKS,
	  D18 as @Specialnost,
      D19 as @FromDate,
	  D20 as @ToDate,
	  D21 as @RoomName,
	  D22 as @ZapovedNo,
	  D23 as @ZapovedDate,
      D24 as @PriceKind,
	  D25 as @ExtraInfo
      from ImportGuests where "Session" = SessionId
      order by N asc do
    
		select GetCounter('GuestCounter') into @GuestNum;
		select IsNull(N, 0) into @CategoryN from Categories where lcase(Category)=lcase(@Category);
		if lcase(@Nationality)='българин' THEN
			set @HumanKind=1;
			if len(@EGN)=10 then
				set @IsEgn='1';
			else
				set @IsEgn='0';
			end if;
		else
			set @HumanKind=2;
			set @IsEgn='0';
			set @SettlementN=0;
		end if;
		select IsNull(N, 0) into @OblastN from Oblasti where lcase(OblastName)=lcase(@Oblast);
		select IsNull(N, 0) into @ObshtinaN from Municipalities where OblastN=@OblastN and lcase(MunicipalityName)=lcase(@Obshtina);
		select IsNull(N, 0) into @SettlementN from Settlements where MunicipalityN=@ObshtinaN and lcase(SettlementName)=lcase(@Selishte);
		if @SettlementN is null then
			set @SettlementN=0;
		end if;
		if lcase(@Gender)='м' then
			set @Sex='M';
		else
			set @Sex='F';
		end if;
		select IsNull(N, 0) into @SpecType from SpecialityTypes where lcase(SpecialityTypeName)=lcase(@OKS);
		select IsNull(N, 0) into @KursN from Kursove where lcase(ShortName)=lcase(@Kurs);
		select IsNull(N, 0) into @SpecNo from Specialities where SpecialityType=@SpecType and lcase(Name)=lcase(@Specialnost);
		select IsNull(N, 0) into @UniN from Universities where lcase(FullName)=lcase(@University);
		
		if (@PassportVal <> '' ) then
			set @PassportValDt=@PassportVal;
		else
			set @PassportValDt = '30.12.1899';
		end if;
		if (@PassportDate<>'') then
			set @PassportDateFloat=(Select DateToFloat(@PassportDate));
		else
			set @PassportDateFloat=0;
		end if;
		
		set @RoomNum=(select IsNull(RoomNum, 0) from Rooms where RoomName=@RoomName);
		set @RoomClass=(select IsNull(RoomCat, 0) from Rooms where RoomName=@RoomName);
		set @PriceKindN=(select IsNull(PriceKind, 0) from PriceKinds where lcase(Description)=lcase(@PriceKind));
		set @errorStr='';
		
		if ((@CategoryN<>0) and (@StudentName<>'') and (@EGN<>'') and (@Adres<>'') 
		and (@PassportNum<>'') and (@Gender<>'') and (@UniN>0) and (@KursN>0) 
		and (@FakNum<>'') and (@OKS<>'') and (@SpecNo>0) and (@FromDate<>'') 
		and (@ToDate<>'') and (@RoomName<>'') and (@ZapovedNo<>'') and (@RoomNum>0) and (@PriceKindN>0)) then
		
			if (Select count(*) from Book b join Guests g on g.GuestNum=b.Guest where g.EGN=@EGN and b.Staying=1)>0 then
				Update ImportGuests set D47=CHAR(13) || CHAR(10) || 'Студентът ' || @StudentName || ' е вече настанен;' where N=@ImpN;
			else
			
				set @FromDateInt=DATEDIFF(DAY, '1899-12-30', CONVERT(DATE, REPLACE(@FromDate, ' г.', ''), 104));
				set @ToDateInt=DATEDIFF(DAY, '1899-12-30', CONVERT(DATE, REPLACE(@ToDate, ' г.', ''), 104));
				set @NumDays=@ToDateInt-@FromDateInt;
				
				if ((@FromDateInt+5)<@loginDTInt) then
					Update ImportGuests set D47=CHAR(13) || CHAR(10) || 'Не може да настаните студентът ' || @StudentName || ' със задна дата;' where N=@ImpN;
				else
					set @IsRoomFull=(select CheckRoomIsFull(@RoomNum));
					if (@IsRoomFull=1) then
						Update ImportGuests set D47=CHAR(13) || CHAR(10) || 'Не може да настаните студентът ' || @StudentName || ', стая ' || @RoomName || ' е запълнена;' where N=@ImpN;
					else
						if @HumanKind=1 then
							INSERT INTO Guests (GuestNum,PassportDate,KPP,Goal,CountryArrive,
							BirthDate,NativeCountry,Reservation,"Group",HasData,CreditLimit,TipVip,
							"Name",EGN,HumanKind,AdultType,Contract,VaulcherNo,TipVaulcher,IsEgnEnch)
							VALUES ( @GuestNum, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
							@StudentName, @EGN, @HumanKind, 0, 0, 0, 'default', @IsEgn);
						else
							INSERT INTO Guests (GuestNum,PassportDate,KPP,Goal,CountryArrive,
							BirthDate,NativeCountry,Reservation,"Group",HasData,CreditLimit,TipVip,
							"Name",EGN,HumanKind,AdultType,Contract,VaulcherNo,TipVaulcher,IsEgnEnch)
							VALUES (@GuestNum, 0, 0, 0, -1, 0, -1, 0, 0, 0, 0, 0,
							@StudentName, @EGN, @HumanKind, 0, 0, 0, 'default', @IsEgn);
						end if;
		
						update Guests set 
						Sex=@Sex,
						NumZN=@ZapovedNo,
						Kurs=@KursN,
						FN=@FakNum,
						SpecNo=@SpecNo,
						UniN=@UniN,
						PassportNumber = @PassportNum,
						PassportDate=@PassportDateFloat,
						PassportWhere = @PassportWhere,
						PaspValDate = @PassportValDt,
						Note2 = @ExtraInfo,
						CategoryN = @CategoryN,
						SettlementN=@SettlementN,
						Address=@Adres,
						KPP=0,
						NameLatin=@StudentNameLat
						where GuestNum=@GuestNum;
		 
						if @ZapovedDate<>'' then
							set @ZapovedDT=CONVERT(DATE, REPLACE(@ZapovedDate, ' г.', ''), 104);
							update Guests set DateZN=@ZapovedDT where GuestNum=@GuestNum;
						end if;
		 
						SELECT PriceList.PriceAll, PriceList.KodCurrency into @RoomPrice, @KodCurrency
						 FROM PriceList,PriceKinds, Registers 
						 WHERE PriceList.PriceKindNo=PriceKinds.PriceKind 
						 AND PriceList.SeasonNo=Registers.IntStojnost 
						 AND Registers.RegisterName='ActivePriceSeason'
						 AND PriceList.ClassNo=@RoomClass AND PriceList.HumanKind=@HumanKind AND PriceList.AdultType=0 AND PriceList.PriceKindNo=@PriceKindN;
		 
						call CheckInGuest(@GuestNum, @FromDateInt, @NumDays, @RoomNum, @PriceKindN, @RoomPrice, @KodCurrency, UserNo);
		 
						set @DocUNP = (select GetUniqueSaleNumber(PrinterName));
						set @PrinterN = (select N from Printers where Name=PrinterName);
						set @SaleOrdersId = (select GetCounter('SaleOrderId'));
						select H.Hotel_ID, H.HotelName into @HotelId, @HotelName from Hotels H, Registers R where R.RegisterName='HotelID' and H.Hotel_ID=R.IntStojnost;
						Insert Into SaleOrders(Id,DocType,UNP,GuestNum,HotelId,StoreName,WorkPlaceId,FiscalDeviceN,UserId,UserCode)
						values (@SaleOrdersId, 0, @DocUNP, @GuestNum, @HotelId, @HotelName, WorkPlaceRegId, @PrinterN, UserNo, UserNo);
						call FillSaleOrderLines_CheckIn(@GuestNum, @SaleOrdersId);
		 
						set bvrCountImportedGuests=bvrCountImportedGuests+1;
					end if;
				end if;
			end if;
			
		else
			if (@CategoryN=0 or @CategoryN is null) then
				set @errorStr='Липсва Категория за ' || @StudentName || ';';
			end if;
			
			if (@EGN='') then
				set @errorStr=@errorStr || CHAR(13) || CHAR(10) || 'Липсва ЕГН за ' || @StudentName || ';';
			end if;
			
			if (@Adres='') then
				set @errorStr=@errorStr || CHAR(13) || CHAR(10) || 'Липсва Адрес за ' || @StudentName || ';';
			end if;
			
			if (@PassportNum='') then
				set @errorStr=@errorStr || CHAR(13) || CHAR(10) || 'Липсва Номер на Паспорт за ' || @StudentName || ';';
			end if;
			
			if (@Gender='') then
				set @errorStr=@errorStr || CHAR(13) || CHAR(10) || 'Липсва Пол за ' || @StudentName || ';';
			end if;
			
			if (@UniN=0 or @UniN is null) then
				set @errorStr=@errorStr || CHAR(13) || CHAR(10) || 'Липсва Висше училище за ' || @StudentName || ';';
			end if;
			
			if (@KursN=0 or @KursN is null) then
				set @errorStr=@errorStr || CHAR(13) || CHAR(10) || 'Липсва Курс за ' || @StudentName || ';';
			end if;
			
			if (@FakNum='') then
				set @errorStr=@errorStr || CHAR(13) || CHAR(10) || 'Липсва Факултетен номер за ' || @StudentName || ';';
			end if;
			
			if (@OKS='') then
				set @errorStr=@errorStr || CHAR(13) || CHAR(10) || 'Липсва ОКС за ' || @StudentName || ';';
			end if;
			
			if (@SpecNo=0 or @SpecNo is null) then
				set @errorStr=@errorStr || CHAR(13) || CHAR(10) || 'Липсва Специалност за ' || @StudentName || ';';
			end if;
			
			if (@FromDate='') then
				set @errorStr=@errorStr || CHAR(13) || CHAR(10) || 'Липсва Начална дата за ' || @StudentName || ';';
			end if;
			
			if (@ToDate='') then
				set @errorStr=@errorStr || CHAR(13) || CHAR(10) || 'Липсва Крайна дата за ' || @StudentName || ';';
			end if;
			
			if (@ZapovedNo='' or @ZapovedNo is null) then
				set @errorStr=@errorStr || CHAR(13) || CHAR(10) || 'Липсва Номер на заповед за настаняване за ' || @StudentName || ';';
			end if;
			
			if (@RoomNum=0 or @RoomNum is null) then
				set @errorStr=@errorStr || CHAR(13) || CHAR(10) || 'Липсва Стая за ' || @StudentName || ';';
			end if;
			
			if (@PriceKindN=0 or @PriceKindN is null) then
				set @errorStr=@errorStr || CHAR(13) || CHAR(10) || 'Липсва Ценоразпис за ' || @StudentName || ';';
			end if;
			
			Update ImportGuests set D47=@errorStr where N=@ImpN;
		end if;
		
  end for;
  
  --delete from ImportGuests where "Session" = SessionId
end

GO