CREATE PROCEDURE dbo.OrderBookSaleReport AS
SET NOCOUNT ON

/*
  Created by SG Oct. 2018 for Konventum
  
  2021-07-30 sg: AssociateFullName hardcoded to "Salg" (kiml@konventum.dk)
  2019-05-09 sg: AddressTypeNumber = 2 (instead of 1)
  2019-04-11 sg: Status from reservations instead of OrderBook, ContactpersonCustomerNumber from address instead of OrderBook
  2019-04-10 sg: All reservations (Lene, Konventum)
  2018-12-05 sg: Fjernet "and cob.ReservationStatus = r.Status"
  2018-11-30 sg: SaleStatus back to previous. New field SaleUserText02 identical with StageName
  2018-11-13 sg: Changed field names, added new field "CompetitorName"
  2018-11-13 sg: SaleStatus always 'Open', include out of house for current year, SaleUserNum03: space when amount is 0
  2018-11-08 sg: Days count taken from hallline instead of reservations
  2018-11-05 sg: More fields added
  2018-10-26 sg: Field name changed from CompUserText01 to SaleUserText01 
*/
Declare @SystemDate DateTime;
Declare @SystemDateStr varchar(8);
Declare @CurrentYear int; --Picasso date

SELECT @SystemDateStr = SYSTEMDATE
     FROM System WITH (NOLOCK)
     WHERE NUMBER = 1;
SET @SystemDate = CONVERT(DATETIME, @SystemDateStr);
Set @CurrentYear = YEAR(@SystemDate); 

select cob.Referencenumber as SaleUserKey01
       , '16PS1101' as IntegrationID
       , cob.ReferenceNumber as Heading
	   , CASE r.Status 
	       WHEN 0 THEN 'Confirmed'
           WHEN 1 THEN 'Provisional'
           WHEN 2 THEN 'Guaranteed'
           WHEN 3 THEN 'Waitinglist'
           WHEN 4 THEN 'Tentative'
           WHEN 5 THEN 'Inhouse'
           WHEN 6 THEN 'OutOfHouse'
           WHEN 7 THEN 'Cancelled'
           WHEN 8 THEN 'NoShow'
           WHEN 9 THEN 'None' 
		 END as StageName
	   , pos.Text as SourceName  
	   , CASE r.Status 
	       WHEN 0 THEN 'Sold'
           WHEN 1 THEN 'Sold'
           WHEN 2 THEN 'Sold'
           WHEN 3 THEN ''
           WHEN 4 THEN 'Open'
           WHEN 5 THEN 'Sold'
           WHEN 6 THEN 'Sold'
           WHEN 7 THEN 'Lost'
           WHEN 8 THEN 'Lost'
           WHEN 9 THEN '' 
		 END as SaleStatus	   
	   , r.ArrivalDate as SaleDate         
       , SUM(TurnoverRooms + TurnOverDaymeetings + TurnOverHalls + TurnOverRestaurant + TurnOverOther) as Amount
	   , CASE r.Status 
	       WHEN 0 THEN 90
           WHEN 1 THEN 90
           WHEN 2 THEN 100
           --WHEN 3 THEN 'Waitinglist'
           WHEN 4 THEN 20
           WHEN 5 THEN 100
           WHEN 6 THEN 100
           WHEN 7 THEN 0
           WHEN 8 THEN 0
           WHEN 9 THEN 0 
		 END as Probability
	   , cob.CustomerNumber as CompUserKey01
	   , a.ContactpersonCustomerNumber as PersUserKey01
	   , a.LastName as SaleUserText01
	   , s.Text as SaleTypeName  --Segment  
	   --, p.FirstName + ' ' + p.LastName as AssociateFullName --Person der har oprettet res.
	   , 'Salg' as AssociateFullName
	   , (SELECT IsNull(DateDiff(dd, MIN(hl.ArrivalDate), MAX(hl.DepartureDate)) + 1, 0) FROM RESERVATIONCONFERENCELINE hl  with (nolock) WHERE hl.ReferenceNumber = cob.ReferenceNumber and IsNull(hl.Plenum, 0) = 1) as SaleUserNum01  --Antal dage
       , (SELECT IsNull(SUM(adults), 0) FROM RESERVATIONCONFERENCELINE hl  with (nolock) WHERE hl.ReferenceNumber = cob.ReferenceNumber and IsNull(hl.Plenum, 0) = 1)  as SaleUserNum02
	   , CASE r.Status
	       WHEN 7 THEN ' '
		   WHEN 8 THEN ' '
		   ELSE Convert(Varchar(20), SUM(TurnoverRooms + TurnOverDaymeetings + TurnOverHalls + TurnOverRestaurant + TurnOverOther))
		 END as SaleUserDec01 
       , pos.Text as CompetitorName		 
	   , CASE r.Status 
	       WHEN 0 THEN 'Confirmed'
           WHEN 1 THEN 'Provisional'
           WHEN 2 THEN 'Guaranteed'
           WHEN 3 THEN 'Waitinglist'
           WHEN 4 THEN 'Tentative'
           WHEN 5 THEN 'Inhouse'
           WHEN 6 THEN 'OutOfHouse'
           WHEN 7 THEN 'Cancelled'
           WHEN 8 THEN 'NoShow'
           WHEN 9 THEN 'None' 
		 END as SaleUserText02
from CustomerOrdreBook cob with (nolock)
INNER JOIN Customer c with (nolock) On cob.CustomerNumber = c.CustomerNumber and c.CustomerTypeNumber = 2 --company
INNER JOIN Reservations r on cob.ReferenceNumber = r.ReferenceNumber and r.GuestNumber = 0
INNER JOIN RESERVATIONMAIN rm  with (nolock) on r.ReferenceNumber = rm.REFERENCENUMBER
INNER JOIN POINTOFSALES pos with (nolock) on r.POINTOFSALE = pos.NUMBER
INNER JOIN SEGMENTS s with (nolock) on r.MARKETSEGMENT = s.NUMBER
INNER JOIN Personel p WITH (NOLOCK) ON rm.StaffID = p.UserIDNumber 
LEFT OUTER JOIN RESERVATIONADDRESSXTAB ax on ax.REFERENCENUMBER = r.ReferenceNumber and ax.GUESTNUMBER = 0 and ax.AddressTypeNumber = 2 --company
INNER JOIN RESERVATIONADDRESS a on ax.ADDRESSNUMBER = a.AddressNumber
where 1=1
  --and (cob.ReservationStatus <> 6 or YEAR(r.ArrivalDate) = @CurrentYear)

group by cob.CustomerNumber
    , cob.ReferenceNumber
	, r.Status
	, pos.Text
	, s.text
	, r.ArrivalDate
	, r.DepartureDate
	, a.ContactpersonCustomerNumber
	, p.FirstName
	, p.LastName
	, a.LASTNAME

	order by cob.ReferenceNumber desc


