+++++++++++++++ Queries before Going Live +++++++++++++++


The following queries are to be only executed BEFORE going live!
(c) protel hotelsoftware GmbH
 


############# Moves #############

select globbnr, *from buch where globbnr > 0 and buchstatus < 2 and reschar <2

 

############## RBD ##############
+++ Searches for different rate codes +++

****** A) Best Western Member Web/CRS reservations ******

SELECT
max(varbuch.buchnr)  'Leistacc Reservierung',
max(ptypgrp.gruppe) 'Preis 1',
max(p_varbuch.gruppe) 'Preis 2'
FROM VARBUCH
inner join buch on buch.leistacc = varbuch.buchnr
inner join ptypgrp on ptypgrp.ptgnr = buch.preistypgr
inner join ptypgrp p_varbuch on p_varbuch.ptgnr = varbuch.ptyp
inner join metadata on metadata.ref = buch.leistacc
where
 buchstatus in (0,1)
 and buch.reschar < 2
 and buch.preistypgr <> varbuch.ptyp and metadata.xkey = 'bwi.confirmationnumber'
group by
varbuch.buchnr


****** B) Group reservations ******

SELECT
max(varbuch.buchnr)  'Leistacc Reservierung',
max(ptypgrp.gruppe) 'Preis 1',
max(p_varbuch.gruppe) 'Preis 2'
FROM VARBUCH
inner join buch on buch.leistacc = varbuch.buchnr
inner join ptypgrp on ptypgrp.ptgnr = buch.preistypgr
inner join ptypgrp p_varbuch on p_varbuch.ptgnr = varbuch.ptyp
where
 buchstatus in (0,1)
 and buch.reschar < 2
 and buch.preistypgr <> varbuch.ptyp and buch.grpmaster > 0 and buch.mpehotel = 1
group by
varbuch.buchnr



 
############# Making rate codes available on the Internet #############
!!! Do not simply apply for use in the WBE!!! 

update ptypgrp set inet = 1 where inet = 0



############# Double PL Numbers #############

select string1, count(string1) from buch where buchstatus < 2 and reschar < 2 group by string1 having count(string1) > 1


************* this is the respective query in order to give the reservations a new 'PL' number, should they be double *************


***** SPE *****
update buch set string1 = 'PL' + convert(varchar, leistacc,0) + UPPER(LEFT(name1,3)) + 'P'
from buch
inner join kunden on kunden.kdnr = buch.kundennr
where string1 in (select string1 from buch group by string1 having COUNT (*) > 1)
 

***** MPE *****
update buch set  string1 = 'PL' + convert(varchar, leistacc,0) + UPPER(LEFT(name1,3)) + 'P' + convert(varchar,buch.mpehotel)
from buch
inner join kunden on kunden.kdnr = buch.kundennr
where string1 in (select string1 from buch group by string1 having COUNT (*) > 1)

<!--- New PL numbers are generated as follows: PL + reservation number + plus the first 3 letters of the guest's last name. --->

***** For test purposes, the following query will return the doubled numbers *****
select string1 from buch group by string1 having COUNT (*) > 1 
(Moves will be shown here as doubled - which is okay)



############# Check guest profiles #############

<!---This query checks if all of the required fields in the guest profile have been filled out--->

select * from kunden where typ=0 and anrede=''
select * from kunden where typ=0 and land=''
select * from kunden where typ=0 and vorname=''

************* The query to update the guest profiles respectively *************

update clients set first name='.' where firstname=" and type=0    <!---Please agree with the client on the text being submitted--->
<!---etc.-->