CREATE PROCEDURE dbo.OrderBookContactReport AS
SET NOCOUNT ON
/*
  Created by SG Oct. 2016

  Changes:
  SG 20171002: Added column "retired" for Customer.MarkAsDeleted
  VD 20170810 GDPR: NoNews is no longer in table Customer
  SG 20170120: Medtage inhouse i back. Medtager outofhouse i "Inhouse", så det i virkeligheder er "året til dato". Canceled og turnaway returnerer antal
  KJ 20161228: Bug: Fix for "'FORMAT' is not a recognized built-in function name.". Case #CR117837
  2016-12-22/SG: Dato format
  2016-12-20/SG: Tilføjet EstablishDate

*/

--Contactperson
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);

DECLARE @IntegrationId VARCHAR(50)
SELECT @IntegrationId = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'HotelName2'
Set @CurrentYear = YEAR(@SystemDate); 

Create Table #Contact
(
  CustomerNumber int,
  CompanyNumber int,
  FirstName varchar(50),
  LastName varchar(60),
  Address1 varchar(100),
  Address2 varchar(100),
  Address3 varchar(100),
  ZipCodeOverwrite varchar(10),
  CityOverWrite varchar(50),
  CountryName varchar(30),
  PhoneNumber varchar(30),
  CellPhoneNumber varchar(30),
  MailAddress varchar(100),
  WWWAddress varchar(100),
  PDFConversion bit,
  TurnoverBack money,
  TurnoverInhouse money,
  TurnoverAhead money,
  TurnoverCancelled int,
  TurnoverTurnaway int,
  EstablishDate DateTime,
  Retired VARCHAR(5),
  IntegrationID VARCHAR(50)
)
INSERT INTO #Contact
SELECT c.CustomerNumber as CustomerNumber, comp.CustomerNumber as CompanyNumber,
       c.FirstName, c.LASTNAME, c.Address1, c.Address2, c.Address3, c.ZipCodeOverwrite, c.CityOverWrite, co.CountryName,
	  c.PhoneNumber, c.CellPhoneNumber, m.MailAddress, c.WWWAddress,
	  c.PDFConversion as MailAsPDF, 
	  0,0,0,0,0, 
	  c.EstablishDate, 
	  CASE c.MarkAsDeleted WHEN 1 Then 'True' WHEN 0 THEN 'False' END As Retired,
	  @IntegrationId
FROM Customer c with (nolock)
LEFT OUTER JOIN Countries co with (nolock) on c.CountryNumber=co.CountryNumber
LEFT OUTER JOIN MailAddresses m with (nolock) on m.CustomerNumber=c.CustomerNumber and m.MailAddressType=0
LEFT OUTER JOIN CustomerLinks cl with (nolock) on c.CustomerNumber = cl.CustomerLinkNumber and cl.LinkTypeNumber = 1
LEFT OUTER JOIN Customer comp with (nolock) on cl.CustomerNumber = comp.CustomerNumber
LEFT OUTER JOIN Segments seg with (nolock) on c.SegmentNumber = seg.NUMBER and seg.MarkAsDeleted = 0
LEFT OUTER JOIN PointOfSales pos with (nolock) on c.PointOfSaleNumber = pos.NUMBER and seg.MarkAsDeleted = 0
LEFT OUTER JOIN GuestTypes gt with (nolock) on c.GuestTypeNumber = gt.TypeNumber and ISNULL(gt.MarkAsDeleted, 0) = 0
WHERE c.CustomerTypeNumber = 8 --contactperson
--  AND c.MarkAsDeleted=0      
order by c.CustomerNumber

UPDATE #Contact SET TurnoverBack =
(SELECT SUM(o.TurnOverDaymeetings + o.TurnOverHalls + o.TurnOverOther + o.TurnOverRestaurant + o.TurnOverRooms)
FROM CustomerOrdreBook o with (nolock)
INNER JOIN #Contact c on o.ContactPersonCustomerNumber = c.CustomerNumber
WHERE o.ContactPersonCustomerNumber = #Contact.CustomerNumber and o.ReservationStatus in  (5,6) --outofhouse, inhouse
AND YEAR(o.DateForValue) < @CurrentYear
Group by o.ContactPersonCustomerNumber)

UPDATE #Contact SET TurnoverAhead =
(SELECT SUM(o.TurnOverDaymeetings + o.TurnOverHalls + o.TurnOverOther + o.TurnOverRestaurant + o.TurnOverRooms)
FROM CustomerOrdreBook o with (nolock)
INNER JOIN #Contact c on o.ContactPersonCustomerNumber = c.CustomerNumber
WHERE o.ContactPersonCustomerNumber = #Contact.CustomerNumber and o.ReservationStatus < 5 --cancelled
Group by o.ContactPersonCustomerNumber)

UPDATE #Contact SET TurnoverInhouse =
(SELECT SUM(o.TurnOverDaymeetings + o.TurnOverHalls + o.TurnOverOther + o.TurnOverRestaurant + o.TurnOverRooms)
FROM CustomerOrdreBook o with (nolock)
INNER JOIN #Contact c on o.ContactPersonCustomerNumber = c.CustomerNumber
WHERE o.ContactPersonCustomerNumber = #Contact.CustomerNumber and o.ReservationStatus in (5,6) --OutOfHouse,inhouse
AND YEAR(o.DateForValue) = @CurrentYear
Group by o.ContactPersonCustomerNumber)

UPDATE #Contact SET TurnoverCancelled =
(SELECT count(distinct o.ReferenceNumber)
FROM CustomerOrdreBook o with (nolock)
INNER JOIN #Contact c on o.ContactPersonCustomerNumber = c.CustomerNumber
INNER JOIN Reservations r with (nolock) on r.Referencenumber = o.ReferenceNumber
WHERE o.ContactPersonCustomerNumber = #Contact.CustomerNumber 
and o.ReservationStatus in  (7, 8) --cancelled, noshow
and IsNull(r.TurnawayNumber, 0) = 0
Group by o.ContactPersonCustomerNumber)

UPDATE #Contact SET TurnoverTurnaway =
(SELECT count(distinct o.ReferenceNumber)
FROM CustomerOrdreBook o with (nolock)
INNER JOIN #Contact c on o.ContactPersonCustomerNumber = c.CustomerNumber
INNER JOIN Reservations r with (nolock) on r.Referencenumber = o.ReferenceNumber
WHERE o.ContactPersonCustomerNumber = #Contact.CustomerNumber 
and o.ReservationStatus in (7, 8) --cancelled, noshow
and IsNull(r.TurnawayNumber, 0) > 0
Group by o.ContactPersonCustomerNumber)


select 
  CustomerNumber,
  CompanyNumber,
  FirstName,
  LastName,
  IsNull(Address1, '') as Address1,
  IsNull(Address2, '') as Address2,
  IsNull(Address3, '') as Address3,
  IsNull(ZipCodeOverwrite, '') as ZipCode,
  IsNull(CityOverWrite, '') as City,
  IsNull(CountryName, '') as CountryName,
  IsNull(PhoneNumber, '') as PhoneNumber,
  IsNull(CellPhoneNumber, '') as CellPhoneNumber,
  IsNull(MailAddress, '') as MailAddress,
  IsNull(WWWAddress, '') as WWWAddress,
  PDFConversion,

  1 - (SELECT CAST (COUNT(*) AS BIT)
  FROM GDPRApproveCustomerXTab X WITH (NOLOCK) 
  WHERE 
    FK_CustomerNumber = #Contact.CustomerNumber AND
    FK_GDPRApproveId = 1 AND 
    ISNULL(DateApprove, 0) > ISNULL(DateReject, 0)) NoNews, 

  IsNull(TurnoverBack, 0) as TurnoverBack,
  IsNull(TurnoverInhouse, 0) as TurnoverInhouse,
  IsNull(TurnoverAhead, 0) as TurnoverAhead,
  IsNull(TurnoverCancelled, 0) as TurnoverCancelled,
  IsNull(TurnoverTurnaway, 0) as TurnoverTurnaway,
  CONVERT(VARCHAR, EstablishDate, 105) + ' ' + CONVERT(VARCHAR, EstablishDate, 108) as EstablishDate,
  Retired,
  IntegrationID
FROM #Contact

DROP TABLE #Contact
