/*
* Inserts or updates a single ItemCodeVanLineMapping.
* Admin uses this stored proc to import ItemCodeVanLineMappings from a file.
*/
CREATE PROCEDURE [dbo].[ItemCodeVanlineMappingImportAction]
    @inVanlineGroupExternalCode varchar(10),
    @inVanlineServiceCode varchar(10),
    @inVanlineCartonCode varchar(10),
    @inItemCode int,
    @inVanlineDescription varchar(64),
    @inOverwriteExistingMapping bit,
    @inReportExistingMappingWarning bit,
    @outErrorMessage varchar(256) output,
    @outRecordsAdded int output,
    @outRecordsUpdated int output
as
set nocount on

set @outErrorMessage = null
set @outRecordsAdded = 0
set @outRecordsUpdated = 0

declare @theErrorCodeID int = 0
declare @VANLINE_SERVICE_CODE_CANNOT_BE_NULL int = 922
declare @ITEMCODE_VANLINE_MAPPING_ALREADY_EXISTS_BUT_SKIPPED int = 923
declare @ITEMCODE_VANLINE_MAPPING_ALREADY_EXISTS_BUT_UPDATED int = 924

if( exists
(
    select top 1 1
    from ItemCodeVanLineMapping
    inner join ItemCode on ItemCode.ICPriKey = ItemCodeVanLineMapping.ItemCodeFID
    inner join VanlineGroup on VanlineGroup.VanlineGroupID = ItemCodeVanLineMapping.VanlineGroupFID
    where ItemCode.ItemCode = @inItemCode and
        VanlineGroup.VanlineExternalCode = @inVanlineGroupExternalCode and
        ItemCodeVanLineMapping.VanlineServiceCode = @inVanlineServiceCode and
        isnull( ItemCodeVanLineMapping.VanlineCartonCode, '' ) = isnull( @inVanlineCartonCode, '' )
) )
begin
    if( @inOverwriteExistingMapping = 1 )
    begin
        update ItemCodeVanLineMapping set
            VanlineServiceCode = @inVanlineServiceCode,
            VanlineCartonCode = case
                when isnull( @inVanlineCartonCode, '' ) = '' then null
                else @inVanlineCartonCode
            end,
            VanlineDescription = case
                when isnull( @inVanlineDescription, '' ) = '' then convert( varchar(64), ItemCode.[Description] )
                else @inVanlineDescription
            end
        from ItemCodeVanLineMapping
        inner join ItemCode on ItemCode.ICPriKey = ItemCodeVanLineMapping.ItemCodeFID
        inner join VanlineGroup on VanlineGroup.VanlineGroupID = ItemCodeVanLineMapping.VanlineGroupFID
        where ItemCode.ItemCode = @inItemCode and
            VanlineGroup.VanlineExternalCode = @inVanlineGroupExternalCode and
            ItemCodeVanLineMapping.VanlineServiceCode = @inVanlineServiceCode and
            isnull( ItemCodeVanLineMapping.VanlineCartonCode, '' ) = isnull( @inVanlineCartonCode, '' )

        set @outRecordsUpdated = @@rowcount

        if( @inReportExistingMappingWarning = 1 )
        begin
            set @theErrorCodeID = @ITEMCODE_VANLINE_MAPPING_ALREADY_EXISTS_BUT_UPDATED
        end
    end
    else if( @inReportExistingMappingWarning = 1 )
    begin
        set @theErrorCodeID = @ITEMCODE_VANLINE_MAPPING_ALREADY_EXISTS_BUT_SKIPPED
    end
end
else
begin
    insert into ItemCodeVanLineMapping
    (
        ItemCodeFID,
        VanlineGroupFID,
        VanlineServiceCode,
        VanlineCartonCode,
        VanlineDescription
    )
    select
        ItemCodeFID = ItemCode.ICPriKey,
        VanlineGroupFID = VanlineGroup.VanlineGroupID,
        VanlineServiceCode = @inVanlineServiceCode,
        VanlineCartonCode = case
            when isnull( @inVanlineCartonCode, '' ) = '' then null
            else @inVanlineCartonCode
        end,
        VanlineDescription = case
            when isnull( @inVanlineDescription, '' ) = '' then convert( varchar(64), ItemCode.[Description] )
            else @inVanlineDescription
        end
    from VanlineGroup
    inner join ItemCode on ItemCode.ItemCode = @inItemCode
    where VanlineGroup.VanlineExternalCode = @inVanlineGroupExternalCode and
        not exists
        (
            select top 1 1
            from ItemCodeVanLineMapping
            inner join ItemCode on ItemCode.ICPriKey = ItemCodeVanLineMapping.ItemCodeFID
            inner join VanlineGroup on VanlineGroup.VanlineGroupID = ItemCodeVanLineMapping.VanlineGroupFID
            where ItemCode.ItemCode = @inItemCode and
                VanlineGroup.VanlineExternalCode = @inVanlineGroupExternalCode and
                ItemCodeVanLineMapping.VanlineServiceCode = @inVanlineServiceCode and
                isnull( ItemCodeVanLineMapping.VanlineCartonCode, '' ) = isnull( @inVanlineCartonCode, '' )
        )

    set @outRecordsAdded = @@rowcount

    if( @outRecordsAdded = 0 )
    begin
        -- Figure out why this was not added.  Probably a duplicate of some previous record
        -- we recently added, which would be why it would not be detected on the validation
        -- pass.
        exec ItemCodeVanlineMappingImportValidation
            @inVanlineGroupExternalCode = @inVanlineGroupExternalCode,
            @inItemCode = @inItemCode,
            @outErrorMessage = @outErrorMessage output

        if( isnull( @outErrorMessage, '' ) = '' )
        begin
            -- if @inVanlineGroupExternalCode and @inItemCode are valid, then
            -- @inVanlineServiceCode must be null since @inVanlineCartonCode and
            -- @inVanlineDescription can both be null.
            set @theErrorCodeID = @VANLINE_SERVICE_CODE_CANNOT_BE_NULL
        end
    end
end

if( @theErrorCodeID > 0 and isnull( @outErrorMessage, '' ) = '' )
begin
    set @outErrorMessage =
    (
        select concat( LogLevel.LevelDescription, ': ', ErrorCode.[Description] )
        from ErrorCode
        inner join LogLevel on LogLevel.LogLevelID = ErrorCode.LogLevelFID
        where ErrorCode.ECPriKey = @theErrorCodeID
    )
end
GO
GRANT EXECUTE ON  [dbo].[ItemCodeVanlineMappingImportAction] TO [MssExec]
GO