CREATE PROCEDURE dbo.OrderBookCompanyReport AS
SET NOCOUNT ON
/*
  Created by SG Oct. 2016 for Konventum

  Changes:
    vd 20181018: EANs now located in a new table.
    SG 20171002: Added column "retired" for Customer.MarkAsDeleted
    VD 20170810 GDPR: NoNews is no longer in table Customer
    SG 20170712: Manglede outofhouse et sted!
    vd 20170419: Housekeeping.
    SG 20170220: Medtager outofhouse i "Inhouse", så det i virkeligheder er "året til dato"
    SG 20170117: Medtage inhouse i back. Tilføjet omsætning for company corp og agenct corp
    KJ 20161228: Bug: Fix for "'FORMAT' is not a recognized built-in function name.". Case #CR117837
    SG 20161222: Each company only once, Customer Corp link corrected, date format
    SG 20161220: Added fields
    KJ 20161129: Ext: Added IntegrationId

*/

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 #Company
(
  CustomerNumber int,
  CustomerType varchar(30),
  CustomerCorpNumber int,
  CompanyName 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,
  CVR varchar(30), 
  EAN varchar(20),
  Segment varchar(30),
  POS varchar(30),
  Guesttype varchar(50),
  TurnoverBack money,       --forrige år
  Turnover2yearBack money,  --2 år siden
  TurnoverInhouse money,
  TurnoverAhead money,      --I år, Status < 5 
  Turnover1yearAhead money,  
  Turnover2yearAhead money,
  TurnoverCancelled int,
  TurnoverTurnaway int,
  EstablishDate DateTime,
  Retired VARCHAR(5),
  IntegrationID VARCHAR(50),
  Dummy integer
)

INSERT INTO #Company
SELECT * FROM (
SELECT c.CustomerNumber as CustomerNumber, 
       CASE c.CustomerTypeNumber
	     WHEN 2 THEN 'Company'
		 WHEN 3 THEN 'Agency'
		 WHEN 4 THEN 'Concern'
		 WHEN 5 THEN 'Agency Corp.'
		 WHEN 6 THEN 'Agent'
	   END as CustomerType,	 
	   cc.CustomerNumber as CustomerCorpNumber,
       c.LASTNAME as CompanyName, c.Address1, c.Address2, c.Address3, c.ZipCodeOverwrite, c.CityOverWrite, co.CountryName,
	   c.PhoneNumber, c.CellPhoneNumber, m.MailAddress, c.WWWAddress, c.PDFConversion as MailAsPDF,  c.CompanyNumber as CVR,
	   ce.EAN as EAN,  --WAS: c.CompanyForm as EAN,
	   
	   seg.Text as Segment, pos.Text as POS, gt.Text as Guesttype,
	   0 as TurnoverBack,
	   0 as Turnover2yearBack,
	   0 as TurnoverInhouse,
	   0 as TurnoverAhead,
	   0 as Turnover1yearAhead,
	   0 as Turnover2yearAhead,
	   0 as TurnoverCancelled,
	   0 as TurnoverTurnaway,
	   cc.EstablishDate,
	   CASE c.MarkAsDeleted WHEN 1 Then 'True' WHEN 0 THEN 'False' END As Retired,
	   @IntegrationId as IntegrationId,
	   ROW_NUMBER() OVER(PARTITION BY c.customernumber ORDER BY cc.customernumber DESC) row
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.CustomerNumber and cl.LinkTypeNumber = 0
LEFT OUTER JOIN Customer cc with (nolock) on cl.CustomerLinkNumber = cc.CustomerNumber and cc.CustomertypeNumber in (4,5) and cc.MarkAsDeleted=0
LEFT OUTER JOIN CustomerEANs ce WITH (NOLOCK) ON ce.CustomerNumber = c.CustomerNumber AND ce.EANType = 0
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 in (2,3,4,5,6)
--  AND c.MarkAsDeleted=0 
 ) a
WHERE row=1
   
--select * from #CustomerLinks

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

--2 år siden
UPDATE #Company SET Turnover2yearBack =
(SELECT SUM(o.TurnOverDaymeetings + o.TurnOverHalls + o.TurnOverOther + o.TurnOverRestaurant + o.TurnOverRooms)
FROM CustomerOrdreBook o with (nolock)
INNER JOIN #Company c on o.CustomerNumber = c.CustomerNumber
WHERE o.CustomerNumber = #Company.CustomerNumber and o.ReservationStatus in (5,6) --outofhouse, inhouse
AND YEAR(o.DateForValue) = @CurrentYear-2
Group by o.Customernumber)

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

--Fremad i år
UPDATE #Company SET TurnoverAhead =
(SELECT SUM(o.TurnOverDaymeetings + o.TurnOverHalls + o.TurnOverOther + o.TurnOverRestaurant + o.TurnOverRooms)
FROM CustomerOrdreBook o with (nolock)
INNER JOIN #Company c on o.CustomerNumber = c.CustomerNumber
WHERE o.CustomerNumber = #Company.CustomerNumber and o.ReservationStatus < 5 
AND YEAR(o.DateForValue) = @CurrentYear
Group by o.Customernumber)

--Næste år
UPDATE #Company SET Turnover1yearAhead =
(SELECT SUM(o.TurnOverDaymeetings + o.TurnOverHalls + o.TurnOverOther + o.TurnOverRestaurant + o.TurnOverRooms)
FROM CustomerOrdreBook o with (nolock)
INNER JOIN #Company c on o.CustomerNumber = c.CustomerNumber
WHERE o.CustomerNumber = #Company.CustomerNumber and o.ReservationStatus < 5 
AND YEAR(o.DateForValue) = @CurrentYear + 1
Group by o.Customernumber)

--2. år frem
UPDATE #Company SET Turnover2yearAhead =
(SELECT SUM(o.TurnOverDaymeetings + o.TurnOverHalls + o.TurnOverOther + o.TurnOverRestaurant + o.TurnOverRooms)
FROM CustomerOrdreBook o with (nolock)
INNER JOIN #Company c on o.CustomerNumber = c.CustomerNumber
WHERE o.CustomerNumber = #Company.CustomerNumber and o.ReservationStatus < 5 
AND YEAR(o.DateForValue) = @CurrentYear + 2
Group by o.Customernumber)

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

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


--Company corp, agency corp
Declare @TempCustomerNumber int;
Declare @TempTurnoverBack money;       --forrige år
Declare @TempTurnover2yearBack money;  --2 år siden
Declare @TempTurnoverInhouse money;
Declare @TempTurnoverAhead money;      --I år, Status < 5 
Declare @TempTurnover1yearAhead money;  
Declare @TempTurnover2yearAhead money;
Declare @TempCancelled int;
Declare @TempTurnaway int;

DECLARE CompanyCorpCur CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT CustomerNumber
  FROM #Company
  WHERE #Company.CustomerType in ('Concern', 'Agency Corp.')

OPEN CompanyCorpCur;
FETCH NEXT FROM CompanyCorpCur INTO @TempCustomerNumber

WHILE(@@FETCH_STATUS <> -1)
BEGIN
  SELECT @TempTurnoverBack = SUM(o.TurnOverDaymeetings + o.TurnOverHalls + o.TurnOverOther + o.TurnOverRestaurant + o.TurnOverRooms)
  FROM CustomerOrdreBook o with (nolock)
  INNER JOIN CustomerLinks cl with (nolock) on o.CustomerNumber = cl.CustomerNumber
  WHERE cl.CustomerLinkNumber = @TempCustomerNumber 
  and o.ReservationStatus in (5,6) --outofhouse, inhouse
  AND YEAR(o.DateForValue) = @CurrentYear-1
  Group by cl.CustomerLinkNumber

  SELECT @TempTurnover2yearBack = SUM(o.TurnOverDaymeetings + o.TurnOverHalls + o.TurnOverOther + o.TurnOverRestaurant + o.TurnOverRooms)
  FROM CustomerOrdreBook o with (nolock)
  INNER JOIN CustomerLinks cl with (nolock) on o.CustomerNumber = cl.CustomerNumber
  WHERE cl.CustomerLinkNumber = @TempCustomerNumber 
  and o.ReservationStatus in (5,6) --outofhouse, inhouse
  AND YEAR(o.DateForValue) = @CurrentYear-2
  Group by cl.CustomerLinknumber

  SELECT @TempTurnoverInhouse = SUM(o.TurnOverDaymeetings + o.TurnOverHalls + o.TurnOverOther + o.TurnOverRestaurant + o.TurnOverRooms)
  FROM CustomerOrdreBook o with (nolock)
  INNER JOIN CustomerLinks cl with (nolock) on o.CustomerNumber = cl.CustomerNumber
  WHERE cl.CustomerLinkNumber = @TempCustomerNumber 
  AND o.ReservationStatus in (5,6) --outofhouse, inhouse
  AND YEAR(o.DateForValue) = @CurrentYear  
  Group by cl.CustomerLinkNumber

  SELECT @TempTurnoverAhead = SUM(o.TurnOverDaymeetings + o.TurnOverHalls + o.TurnOverOther + o.TurnOverRestaurant + o.TurnOverRooms)
  FROM CustomerOrdreBook o with (nolock)
  INNER JOIN CustomerLinks cl with (nolock) on o.CustomerNumber = cl.CustomerNumber
  WHERE cl.CustomerLinkNumber = @TempCustomerNumber 
  and o.ReservationStatus < 5 
  AND YEAR(o.DateForValue) = @CurrentYear  
  Group by cl.CustomerLinkNumber

  SELECT @TempTurnover1yearAhead = SUM(o.TurnOverDaymeetings + o.TurnOverHalls + o.TurnOverOther + o.TurnOverRestaurant + o.TurnOverRooms)
  FROM CustomerOrdreBook o with (nolock)
  INNER JOIN CustomerLinks cl with (nolock) on o.CustomerNumber = cl.CustomerNumber
  WHERE cl.CustomerLinkNumber = @TempCustomerNumber 
  and o.ReservationStatus < 5 
  AND YEAR(o.DateForValue) = @CurrentYear + 1
  Group by cl.CustomerLinkNumber

  SELECT @TempTurnover2yearAhead = SUM(o.TurnOverDaymeetings + o.TurnOverHalls + o.TurnOverOther + o.TurnOverRestaurant + o.TurnOverRooms)
  FROM CustomerOrdreBook o with (nolock)
  INNER JOIN CustomerLinks cl with (nolock) on o.CustomerNumber = cl.CustomerNumber
  WHERE cl.CustomerLinkNumber = @TempCustomerNumber 
  and o.ReservationStatus < 5 
  AND YEAR(o.DateForValue) = @CurrentYear + 2
  Group by cl.CustomerLinkNumber

  SELECT @TempCancelled = count(distinct o.ReferenceNumber)
  FROM CustomerOrdreBook o with (nolock)
  INNER JOIN CustomerLinks cl with (nolock) on o.CustomerNumber = cl.CustomerNumber
  INNER JOIN Reservations r with (nolock) on r.Referencenumber = o.ReferenceNumber
  WHERE cl.CustomerLinkNumber = @TempCustomerNumber 
  and o.ReservationStatus in  (7, 8) --cancelled, noshow
  and IsNull(r.TurnawayNumber, 0) = 0
  Group by cl.CustomerLinkNumber

  SELECT @TempTurnaway = count(distinct o.ReferenceNumber)
  FROM CustomerOrdreBook o with (nolock)
  INNER JOIN CustomerLinks cl with (nolock) on o.CustomerNumber = cl.CustomerNumber
  INNER JOIN Reservations r with (nolock) on r.Referencenumber = o.ReferenceNumber
  WHERE cl.CustomerLinkNumber = @TempCustomerNumber 
  and o.ReservationStatus in  (7, 8) --cancelled, noshow
  and IsNull(r.TurnawayNumber, 0) > 0
  Group by cl.CustomerLinkNumber

  UPDATE #Company set TurnoverBack =       @TempTurnoverBack, 
                      Turnover2yearBack =  @TempTurnover2yearBack,
				  TurnoverInhouse =    @TempTurnoverInhouse,
				  TurnoverAhead =      @TempTurnoverAhead,
				  Turnover1yearAhead = @TempTurnover1yearAhead,
				  Turnover2yearAhead = @TempTurnover2yearAhead,
				  TurnoverCancelled  = @TempCancelled,
				  TurnoverTurnaway   = @TempTurnaway

  WHERE CustomerNumber = @TempCustomerNumber

  Set @TempCustomerNumber = 0.0
  Set @TempTurnoverBack = 0.0
  Set @TempTurnover2yearBack = 0.0
  Set @TempTurnoverInhouse = 0.0
  Set @TempTurnoverAhead = 0.0
  Set @TempTurnover1yearAhead = 0.0  
  Set @TempTurnover2yearAhead = 0.0

  FETCH NEXT FROM CompanyCorpCur INTO @TempCustomerNumber
End

select 
  CustomerNumber,
  CustomerType,
  CustomerCorpNumber,
  CompanyName,
  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 = #Company.CustomerNumber AND
    FK_GDPRApproveId = 1 AND 
    ISNULL(DateApprove, 0) > ISNULL(DateReject, 0)) NoNews, 

  IsNull(CVR, '') as CVR, 
  IsNull(EAN, '') as EAN, 
  IsNull(Segment, '') as Segment,
  IsNull(POS, '') as POS,
  IsNull(Guesttype, '') as Guesttype,
  IsNull(TurnoverBack, 0) as TurnoverBack,
  IsNull(Turnover2yearBack, 0) as Turnover2yearBack,
  IsNull(TurnoverInhouse, 0) as TurnoverInhouse,
  IsNull(TurnoverAhead, 0) as TurnoverAhead,
  IsNull(Turnover1yearAhead, 0) as Turnover1yearAhead,
  IsNull(Turnover2yearAhead, 0) as Turnover2yearAhead,
  IsNull(TurnoverCancelled, 0) as TurnoverCancelled,
  IsNull(TurnoverTurnaway, 0) as TurnoverTurnaway,
  CONVERT(VARCHAR, EstablishDate, 105) + ' ' + CONVERT(VARCHAR, EstablishDate, 108) as EstablishDate,
  Retired,
  IntegrationID
FROM #Company

DROP TABLE #Company
