-- =============================================
-- Author:     Jeff Spindler
-- Date: 2/2022
-- Description: Updates TradeShow and Shows table
-- Note: Internal procedure not called directly from Roadload
-- =============================================
CREATE PROCEDURE [rdld].[UpdateTradeShows]
@OrdPriKey int,
@TradeShowsXML xml
AS
BEGIN
set nocount on

-- run thru the new list

if CURSOR_STATUS('local','rowCursor') >= -1
begin
   deallocate rowCursor;
end

DECLARE @xmlHandle INT;
EXEC sp_xml_preparedocument @xmlHandle OUTPUT,
                            @TradeShowsXML,
                            '<REGI xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>';

declare rowCursor CURSOR LOCAL FAST_FORWARD
for select *
   from OpenXml(@xmlHandle,'/ArrayOfTradeShowRowExport/TradeShowRowExport', 2)
   with ( --                                  @ lets xmlattribute work with ,2)
          OriginDestinationFlag smallint      '@OriginDestinationFlag',
         ShowCode              varchar(4)    '@ShowCode',
         TradeShowName         varchar(50)   '@TradeShowName',
         Exhibitor             varchar(20)   '@Exhibitor',
         BoothNumber           varchar(20)   '@BoothNumber',
         BoothDescription      varchar(64)   '@BoothDescription',
         SequenceNumber        smallint      '@SequenceNumber'
      );

declare @OriginDestinationFlag smallint       ;
declare @ShowCode              varchar(4)             ;
declare @TradeShowName         varchar(50)      ;
declare @Exhibitor             varchar(20)     ;
declare @BoothNumber           varchar(20)     ;
declare @BoothDescription      varchar(64)      ;
declare @SequenceNumber        smallint       ;

declare @outShowID int;

declare @TradeShowIds table
(
    id int
);


open rowCursor;
declare @fetch_had_status int = 0 ;
while @fetch_had_status = 0
begin
        
   fetch next from rowCursor into
      @OriginDestinationFlag      ,
      @ShowCode            ,
      @TradeShowName       ,
      @Exhibitor        ,
      @BoothNumber        ,
      @BoothDescription            ,
      @SequenceNumber;

   set @fetch_had_status = @@FETCH_STATUS
   if @fetch_had_status = 0
   begin
      DECLARE @isInShows int = (SELECT count(*) FROM Shows WHERE Code = @ShowCode);
      IF( @isInShows = 0)
      BEGIN
         EXEC    [dbo].[AddShow]
              @inCode = @ShowCode,
              @inDescription = @TradeShowName,
              @outShowID = @outShowID OUTPUT
      END;
      DECLARE @id int =
         (SELECT TradeShowID FROM TradeShow
            WHERE OrdersFID = @OrdPriKey and ShowCode = @ShowCode and SequenceNumber = @SequenceNumber);
      IF( @id is not null)
      BEGIN
         UPDATE TradeShow
            SET Exhibitor = @Exhibitor,
            BoothNumber = @BoothNumber,
            BoothDescription = @BoothDescription,
            OriginDestinationFlag = @OriginDestinationFlag
         WHERE TradeShowID = @id;
      END
      ELSE
      BEGIN
         INSERT TradeShow ( OrdersFID,  ShowCode, TradeShowName,  Exhibitor,  BoothNumber,  BoothDescription,  OriginDestinationFlag,  SequenceNumber)
         values           (@OrdPriKey, @ShowCode,@TradeShowName, @Exhibitor, @BoothNumber, @BoothDescription, @OriginDestinationFlag, @SequenceNumber);
         SET @id = SCOPE_IDENTITY();
      END
      insert into @TradeShowIds (id) values (@id);
   end
end

delete from TradeShow
   where TradeShow.OrdersFID = @OrdPriKey and
      TradeShow.ShowCode = @ShowCode and
      TradeShow.TradeShowID not in (select id from @TradeShowIds)

close rowCursor;
deallocate rowCursor;
                                  

EXEC sp_xml_removedocument @xmlHandle;

return 0;
end;
GO
GRANT EXECUTE ON  [rdld].[UpdateTradeShows] TO [MssExec]
GO