-- =============================================
-- Author:     Jeff Spindler
-- Date: 2/2022
-- Description: Updates SCShippingDetail
-- Note: Internal procedure not called directly from Roadload
--
-- SCShippingDetail is surfaced in the MoversSuite UI and in Roadload as 'Product Details'
--
--<?xml version="1.0" encoding="utf-16"?>
--<ArrayOfShippingDetailsRowExport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
--  <ShippingDetailsRowExport Description="57410-4 D:77x49x73 - 9140#" Length="77" Width="49" Height="73" />
--  <ShippingDetailsRowExport Description="DISPLAYS &amp; RELATED ARTICLES" Length="" Width="" Height="" />
--  <ShippingDetailsRowExport Description="57410-3 D:44x49x73 - 2070#" Length="44" Width="49" Height="73" />
--</ArrayOfShippingDetailsRowExport>
-- =============================================
CREATE PROCEDURE [rdld].[UpdateShippingDetails]
@OrdPriKey int,
@ShippingDetailsXML 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,
                            @ShippingDetailsXML,
                            '<REGI xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>';

declare rowCursor CURSOR LOCAL FAST_FORWARD
for select *
   from OpenXml(@xmlHandle,'/ArrayOfShippingDetailsRowExport/ShippingDetailsRowExport', 2)
   with ( --                                  @ lets xmlattribute work with ,2)
          [Description]        varchar(80)  '@Description',
         [Length]             varchar(10)   '@Length',
         Width                varchar(10)   '@Width',
         Height               varchar(10)   '@Height',
         SequenceNumber       int           '@SequenceNumber'
      );

declare @Description varchar(80)    ;
declare @LengthStr      varchar(10) ;
declare @WidthStr       varchar(10) ;
declare @HeightStr      varchar(10) ;
declare @Length      int  = null    ;
declare @Width       int = null     ;
declare @Height      int = null     ;
declare @SequenceNumber smallint    ;

declare @outID int;

declare @ShippingDetailIds table
(
    id int
);


open rowCursor;
declare @fetch_had_status int = 0 ;
while @fetch_had_status = 0
begin
        
   fetch next from rowCursor into
      @Description ,
      @LengthStr   ,
      @WidthStr    ,
      @HeightStr   ,
      @SequenceNumber;

   set @fetch_had_status = @@FETCH_STATUS
   if @fetch_had_status = 0
   begin
      Set @Length = Cast(Nullif(@LengthStr, '') as int);
      Set @Width =  Cast(Nullif(@WidthStr, '') as int);
      Set @Height = Cast(Nullif(@HeightStr, '') as int);

      DECLARE @id int =
         (SELECT PriKey FROM SCShippingDetails
            WHERE OrdPriKey = @OrdPriKey
              and SCShippingDetails.[SequenceNumber] = @SequenceNumber);
      IF( @id is not null)
         BEGIN
            UPDATE SCShippingDetails
               SET [Length] = @Length,
               Width = @Width,
               Height = @Height,
               [Description] = @Description  -- in case they changed case
            WHERE SCShippingDetails.PriKey = @id;
         END
      ELSE
         BEGIN            
            INSERT SCShippingDetails ( OrdPriKey,  [Description], [Length],  Width,  Height, SequenceNumber)
            values                   (@OrdPriKey, @Description,   @Length,  @Width,  @Height, @SequenceNumber);
         SET @id = SCOPE_IDENTITY();
      END
      insert into @ShippingDetailIds (id) values (@id);
   end
end

delete from SCShippingDetails
where SCShippingDetails.OrdPriKey = @OrdPriKey and
    SCShippingDetails.PriKey not in (select id from @ShippingDetailIds);

close rowCursor;
deallocate rowCursor;
                                  

EXEC sp_xml_removedocument @xmlHandle;

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