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 first N 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 first N 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