/**
* Description: Gets the active plastic provider(s) for the specified branch
* that meet the optional criteria.
*
* PROGRAMMER'S NOTE:
* ==========================================================================
* If you modify this stored proc to add new fields, you probably MUST also
* modify the GetPlasticProvider stored proc to return the same fields.
* ==========================================================================
*/
CREATE procedure [dbo].[GetPlasticProvidersByBranch]
    @inBranchId int,
    @inCanDoNewCards bit = null,
    @inCanDoNewSales bit = null
as
set nocount on

declare @theNewCardsAreDisabledSetting bit = case @inCanDoNewCards
    when 1 then 0
    when 0 then 1
    else null
end

declare @theNewSalesAreDisabledSetting bit = case @inCanDoNewSales
    when 1 then 0
    when 0 then 1
    else null
end

select
    ProviderId = PlasticProvider.ProviderID,
    UserName = PlasticProvider.UserName,
    [Credentials] = PlasticProvider.[Credentials],
    ClientId = PlasticProvider.ClientId,
    AccountCode = PlasticProvider.AccountCode,
    CanHandleCards = PlasticProvider.CanHandleCards,
    CanHandleChecking = PlasticProvider.CanHandleChecking,
    CanHandleSavings = PlasticProvider.CanHandleSavings,
    HppBaseUrl = PlasticProvider.HppBaseUrl,
    Active = convert( bit, case PlasticProvider.Inactive when 1 then 0 else 1 end ),
    DisableNewInstruments = PlasticProvider.DisableNewInstruments,
    DisableNewSales = PlasticProvider.DisableNewSales,
    --StatementDescriptor = PlasticProvider.StatementDescriptor,
    CurrencyCode = PlasticProvider.CurrencyCode,
    ProviderName = PlasticProvider.Provider,
    ProviderBrand = PlasticProviderType.Brand,
    AllowWebPayments = convert( bit, isnull( PlasticWebProvider.AllowWebPayments, 0 ) ),
    WebCompanyCode = PlasticWebProvider.WebCompanyCode,
    ProviderTypeCode = PlasticProviderType.TypeCode,
    ProviderValidationKeyCode = concat( PlasticProviderType.TypeCode, case when PlasticProvider.UseProductionUrl = 0 then 'TEST' else 'PROD' end ),
    UseMoversConnectCredentials = PlasticProvider.UseMoversConnectCredentials,
    UseInternalBrowserControlForHostedPaymentPages = convert( bit, case GlobalSystemOption.[Value] when 'true' then 1 else 0 end ),
    UsesSeparateNameFields = PlasticProviderType.UsesSeparateNameFields,
    CutoffTime = PlasticProvider.CutoffTime,
    IsSaturdayBusinessDay = PlasticProvider.IsSaturdayBusinessDay,
    IsSundayBusinessDay = PlasticProvider.IsSundayBusinessDay,
    SupportsAuthCapture = convert( bit, case
        when PlasticProviderType.SupportsAuthCapture = 1 and PlasticProvider.AllowAuthorizations = 1 then 1
        else 0
    end ),
    WarningCaptureDays = isnull( PlasticProvider.WarningCaptureDays, 365000 ),
    MaxCaptureDays = isnull( PlasticProvider.MaxCaptureDays, 365000 )
from PlasticProviderBranchMapping
inner join PlasticProvider on PlasticProvider.ProviderID = PlasticProviderBranchMapping.ProviderFID
inner join PlasticProviderType on PlasticProviderType.ProviderTypeID = PlasticProvider.ProviderTypeFID
left outer join PlasticWebProvider on PlasticWebProvider.ProviderFID = PlasticProvider.ProviderID
left outer join GlobalSystemOption on GlobalSystemOption.[Name] = 'UseInternalBrowserControlForHostedPaymentPages'
where PlasticProviderBranchMapping.BranchFID = @inBranchId and
    PlasticProvider.Inactive = 0 and
    ( PlasticProvider.DisableNewInstruments = @theNewCardsAreDisabledSetting or @theNewCardsAreDisabledSetting is null ) and
    ( PlasticProvider.DisableNewSales = @theNewSalesAreDisabledSetting or @theNewSalesAreDisabledSetting is null )
order by isnull( PlasticProvider.PreferenceOrder, 999999999 ), PlasticProvider.ProviderID desc
GO
GRANT EXECUTE ON  [dbo].[GetPlasticProvidersByBranch] TO [MssExec]
GO