CREATE PROCEDURE dbo.GenerateMISTotals @FromDate DateTime AS
/*
  Created by Svend Grønlund, 2015-09-04
  Description:	Creates totals of MIS per day in table MISTotals. 
  SystemDate is the Picasso date + the current system time (hour, minutes)
  Changes:
    KJ 20240529: Ext: Added FolioNumber and GuestNumber 
    KJ 20231218: Ext: Added Guesttype, purposeCode and country
    20210302: Added ware count fields
    2021-02-15, SG: Added WareNumber, StaffId,and ReferenceNumber
                    STOP updating MISTotals, as all information is now in MISHistory 
    20200615: KJ: Insert -1 as FK_WareNumber
    2020-04-01, SG: Exclude ststus 7, 8 because these are now included i MISTABLE 
    2019-11-11, SG: Default for MISHistoryPeriod changed from 0 to 731 days.
    2018-02-16: SG: Added @NightAuditRunning, MISTotals cleanup
    2018-02-01: KJ: Added MISHistory cleanup
    2018-01-22 SG: Added MISHistory part
    2016-03-11 SG: Time in systemDate was not correct
*/
SET NOCOUNT ON

DECLARE @SystemDateStr CHAR(14)
DECLARE @SystemDate DATE
DECLARE @MISHistoryPeriod VARCHAR(5)
DECLARE @SystemDateRecords INT
DECLARE @NightAuditRunning INT
   
SELECT @MISHistoryPeriod = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'MISHistoryPeriod'
SET @MISHistoryPeriod = ISNULL(@MISHistoryPeriod, 731)

SELECT @SystemDateStr = SystemDate FROM System WITH (NOLOCK) WHERE Number = 1
SET @SystemDate = CONVERT(DATE, @SystemDateStr)

SELECT @SystemDateRecords = COUNT(*) FROM MISHistory WITH (NOLOCK) WHERE SystemDate = @SystemDate 
   
SELECT @NightAuditRunning = NightAuditRunning FROM Terminals WITH (NOLOCK) WHERE NightAuditRunning = 1
SET @NightAuditRunning = ISNULL(@NightAuditRunning, 0)
   
--   Print ' @SystemDate = ' + convert(char(23), @SystemDate, 126) + ' @SystemDateRecords=' + convert(varchar, @SystemDateRecords)
--   PRINT DATEADD(DAY, -CAST(@MISHistoryPeriod AS INT), @SystemDate)
--Only calculate when MIS is calculated from nightaudit or from systemdate ("Normal")
   
IF CONVERT(INT, @MISHistoryPeriod) > 0 AND (@SystemDate = @FromDate OR @NightAuditRunning = 1) AND @SystemDateRecords = 0
BEGIN 
  INSERT INTO MISHistory
  SELECT @SystemDate, DateStamp, HotelCustomerNumber, DepartmentNumber, AccountNumber, SegmentNumber, PointOfSaleNumber, ISNULL(FK_PurposeCodeNumber, 0),
  ISNULL(FK_GuestTypeNumber, 0), ISNULL(MISTable.ProductNumber, 0), Room_HallType, MISTable.RoomTypeNumber, ValueType,
  SUM(BookedAdult), SUM(BookedChildren1), SUM(BookedChildren2), SUM(BookedNumberOfRooms),
  SUM(BookedPrice), SUM(BookedVatOfPrice), SUM(BookedPackage), SUM(BookedVatOfPackage),
  ISNULL(WareNumber, 0), ISNULL(StaffId, 0), ISNULL(MISTable.ReferenceNumber, 0), SUM(ActualWareCount), SUM(BookedWareCount), ISNULL(CampaignCode, 0), ISNULL(ProjectCode, 0), ISNULL(ReservationStatus, 9),
  ISNULL(FK_CountryNumber, 0), ISNULL(FolioNumber, 0), ISNULL(GuestNumber, 0)
  FROM MISTable WITH (NOLOCK)
  WHERE DateStamp >= @SystemDate
	AND ReservationStatus NOT IN (7,8)
  GROUP BY DateStamp, HotelCustomerNumber, DepartmentNumber, AccountNumber, SegmentNumber, PointOfSaleNumber, ISNULL(FK_PurposeCodeNumber, 0), ISNULL(FK_GuestTypeNumber, 0), 
  ISNULL(MISTable.ProductNumber, 0), Room_HallType, MISTable.RoomTypeNumber, ValueType,	WareNumber, StaffId, MISTable.ReferenceNumber, ISNULL(CampaignCode, 0), ISNULL(ProjectCode, 0), ReservationStatus, FK_CountryNumber, FolioNumber, GuestNumber
END

DELETE MISHistory WHERE SystemDate < DATEADD(DAY, -CAST(@MISHistoryPeriod AS INT), @SystemDate)
DELETE MISTotals WHERE SystemDate < DATEADD(DAY, -CAST(@MISHistoryPeriod AS INT), @SystemDate)