CREATE PROCEDURE dbo.GenerateMISTotals @FromDate DateTime
AS
-- =============================================
-- Author:		SG
-- Create date: 2015-09-04
-- Description:	Creates totals of MIS per day in table MISTotals. 
-- SystemDate is the Picasso date + the current system time (hour, minutes)
-- =============================================
-- 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
BEGIN
-- =============================================
-- MISHistory:
-- Create date: 2018-01-23
-- Description:	Stores totals of booked values from MIS in table MISHistory. Only once per day. Only when FromDate is SystemDate
-- =============================================

   Declare @SystemDateStr Char(14);
   Declare @SystemDate Date;
   Declare @MISHistoryPeriod varchar(5);
   Declare @SystemDateRecords Integer;
   Declare @NightAuditRunning Integer;
   
   SELECT @MISHistoryPeriod = KeyValue FROM HotelSetup (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 (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(Integer, @MISHistoryPeriod) > 0 And (@SystemDate = @FromDate Or @NightAuditRunning = 1) 
                                              And @SystemDateRecords = 0
   BEGIN 
     INSERT INTO MISHistory
       SELECT @SystemDate AS SystemDate, DateStamp, HotelCustomerNumber, DepartmentNumber, AccountNumber, 
	          SegmentNumber, PointOfSaleNumber, IsNull(PurposeCode, 0) AS PurposeCodeNumber, IsNull(GuestType, 0) AS GuestTypeNumber, 
			  IsNull(MISTable.ProductNumber, 0) AS ProductNumber, Room_HallType, MISTable.RoomTypeNumber, ValueType,
       SUM(BookedAdult) AS Adult,
       SUM(BookedChildren1) AS Children1,
       SUM(BookedChildren2) AS Children2,
       SUM(BookedNumberOfRooms) AS NumberOfRooms,
       SUM(BookedPrice) AS Price,
       SUM(BookedVatOfPrice) AS VATOfPrice,
       SUM(BookedPackage) AS PackageAmount,
       SUM(BookedVatOfPackage) AS VATOfPackageAmount,
       IsNull(WareNumber, 0),
	   IsNull(StaffId, 0),
	   IsNull(MISTABLE.ReferenceNumber, 0),
	   SUM(ActualWareCount),
     SUM(BookedWareCount)
       FROM MISTable WITH (NOLOCK)
       LEFT OUTER JOIN Reservations WITH (NOLOCK) ON MISTable.ReferenceNumber = Reservations.ReferenceNumber 
            AND Reservations.GuestNumber = 0
       WHERE DateStamp >= @SystemDate
	   and ReservationStatus NOT IN (7,8)
       GROUP BY DateStamp, HotelCustomerNumber, DepartmentNumber, AccountNumber, 
	          SegmentNumber, PointOfSaleNumber, IsNull(PurposeCode, 0), IsNull(GuestType, 0), 
			  IsNull(MISTable.ProductNumber, 0), Room_HallType, MISTable.RoomTypeNumber, ValueType,
			  WareNumber, StaffId, MISTABLE.ReferenceNumber
   END;

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