USP_REVENUEBATCH_SEPAMANDATE_VALIDATEORCREATE

CREATE procedure dbo.USP_REVENUEBATCH_SEPAMANDATE_VALIDATEORCREATE
    @SEPAMANDATEID as uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,
    @CURRENTDATE datetime = null,
    @ADDSEPAMANDATE bit = 0,
    @SEPAMANDATECUSTOMIDENTIFIER as nvarchar(35) = '',
    @SEPAMANDATESIGNATUREDATE as date,
    @SEPAMANDATETYPECODE as tinyint,
    @PAYMENTMETHODCODE as tinyint,
    @CONSTITUENTACCOUNTID as uniqueidentifier,
    @FINANCIALINSTITUTIONID as uniqueidentifier,
    @TRANSACTIONCURRENCYID as uniqueidentifier
as
begin
    declare @USEEXISTINGSEPAMANDATE bit = 0;
    if @SEPAMANDATEID is null
    begin
        -- Attempt to locate matching SEPA Mandate

        select @SEPAMANDATEID = SEPAMANDATE.ID
        from dbo.SEPAMANDATE
        where
            (
                @SEPAMANDATECUSTOMIDENTIFIER = SEPAMANDATE.LOOKUPID or
                @SEPAMANDATECUSTOMIDENTIFIER = SEPAMANDATE.CUSTOMIDENTIFIER
            ) and
            @SEPAMANDATETYPECODE = SEPAMANDATE.TYPECODE and
            @SEPAMANDATESIGNATUREDATE = SEPAMANDATE.SIGNATUREDATE and
            @CONSTITUENTACCOUNTID = SEPAMANDATE.CONSTITUENTACCOUNTID;

        if @SEPAMANDATEID is not null
        begin
            set @USEEXISTINGSEPAMANDATE = 1;
            set @ADDSEPAMANDATE = 0;
        end
    end
    else
    begin
        if exists (select 1 from dbo.SEPAMANDATE where SEPAMANDATE.ID = @SEPAMANDATEID)
            set @USEEXISTINGSEPAMANDATE = 1;
    end

    --Set defaults

    if @ADDSEPAMANDATE is null
        set @ADDSEPAMANDATE = 0;

    -- Bail if no SEPA mandate is being used

    if @USEEXISTINGSEPAMANDATE = 0 and @ADDSEPAMANDATE = 0
        return 0;

    --Set more defaults

    if @SEPAMANDATECUSTOMIDENTIFIER is null
        set @SEPAMANDATECUSTOMIDENTIFIER = '';
    if @SEPAMANDATETYPECODE is null
        set @SEPAMANDATETYPECODE = 0;
    if @CHANGEAGENTID is null
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
    if @CURRENTDATE is null
        set @CURRENTDATE = getdate();

    -- Attempt to find the institution from an existing constituent account

    if @FINANCIALINSTITUTIONID is null
    begin
        select @FINANCIALINSTITUTIONID = FINANCIALINSTITUTIONID
        from dbo.CONSTITUENTACCOUNT
        where ID = @CONSTITUENTACCOUNTID;
    end

    -- If a financial institution is not provided, attempt to find one in revenue batch

    if @FINANCIALINSTITUTIONID is null
    begin
        select @FINANCIALINSTITUTIONID = FINANCIALINSTITUTIONID
        from dbo.BATCHREVENUECONSTITUENTACCOUNT
        where ID = @CONSTITUENTACCOUNTID;
    end

    -- If a financial institution is not provided, attempt to find one in sponsorship batch

    if @FINANCIALINSTITUTIONID is null
    begin
        select @FINANCIALINSTITUTIONID = FINANCIALINSTITUTIONID
        from dbo.BATCHSPONSORSHIPCONSTITUENTACCOUNT
        where ID = @CONSTITUENTACCOUNTID;
    end

    declare @BANKINGSYSTEMCONDITIONSETTINGNAME nvarchar(50) = '';
    select
        @BANKINGSYSTEMCONDITIONSETTINGNAME = BANKINGSYSTEM.CONDITIONSETTINGNAME
    from
        dbo.FINANCIALINSTITUTION
        inner join dbo.BANKINGSYSTEM on FINANCIALINSTITUTION.BANKINGSYSTEMID = BANKINGSYSTEM.ID
    where
        FINANCIALINSTITUTION.ID = @FINANCIALINSTITUTIONID;

    if @BANKINGSYSTEMCONDITIONSETTINGNAME = ''
    begin
        select @BANKINGSYSTEMCONDITIONSETTINGNAME = BANKINGSYSTEM.CONDITIONSETTINGNAME
        from dbo.BATCHREVENUEFINANCIALINSTITUTION
            inner join dbo.BANKINGSYSTEM on BATCHREVENUEFINANCIALINSTITUTION.BANKINGSYSTEMID = BANKINGSYSTEM.ID
        where BATCHREVENUEFINANCIALINSTITUTION.ID = @FINANCIALINSTITUTIONID;
    end

    declare @TRANSACTIONCURRENCYISEURO bit = 0;
    if exists (select 1 from dbo.CURRENCY where CURRENCY.ID = @TRANSACTIONCURRENCYID and CURRENCY.ISO4217 = N'EUR')
        set @TRANSACTIONCURRENCYISEURO = 1;

    --Several conditions must be met before the mandate field is enabled

    if (@PAYMENTMETHODCODE <> 3) --Direct debit

    begin
        if @ADDSEPAMANDATE = 1
            raiserror('BBERR_SEPAMANDATE_ADDNOTALLOWED_WRONGPAYMENTMETHOD', 13, 1);
        else if @USEEXISTINGSEPAMANDATE = 1
            raiserror('BBERR_SEPAMANDATE_NOTALLOWED_WRONGPAYMENTMETHOD', 13, 1);
        return 1;
    end

    if (@CONSTITUENTACCOUNTID is null) --With a constituent account

    begin
        if @ADDSEPAMANDATE = 1
            raiserror('BBERR_SEPAMANDATE_ADDNOTALLOWED_NOACCOUNT', 13, 1);
        else if @USEEXISTINGSEPAMANDATE = 1
            raiserror('BBERR_SEPAMANDATE_NOTALLOWED_NOACCOUNT', 13, 1);
        return 1;
    end

    if (@BANKINGSYSTEMCONDITIONSETTINGNAME <> N'BankingSystem-SEPA') --Using a SEPA financial institution

    begin
        if @ADDSEPAMANDATE = 1
            raiserror('BBERR_SEPAMANDATE_ADDNOTALLOWED_WRONGBANKINGSYSTEM', 13, 1);
        else if @USEEXISTINGSEPAMANDATE = 1
            raiserror('BBERR_SEPAMANDATE_NOTALLOWED_WRONGBANKINGSYSTEM', 13, 1);
        return 1;
    end

    if (@TRANSACTIONCURRENCYISEURO <> 1) --With a euro transaction currency

    begin
        if @ADDSEPAMANDATE = 1
            raiserror('BBERR_SEPAMANDATE_ADDNOTALLOWED_WRONGCURRENCY', 13, 1);
        else if @USEEXISTINGSEPAMANDATE = 1
            raiserror('BBERR_SEPAMANDATE_NOTALLOWED_WRONGCURRENCY', 13, 1);
        return 1;
    end

    if @ADDSEPAMANDATE = 1 and @USEEXISTINGSEPAMANDATE = 0
    begin
        -- Validate custom identifier if one was provided

        if @SEPAMANDATECUSTOMIDENTIFIER <> '' and
             (
                @SEPAMANDATECUSTOMIDENTIFIER not like '[a-zA-Z0-9]%' or                        -- Starts with alphanumeric

                @SEPAMANDATECUSTOMIDENTIFIER like '%[^a-zA-Z0-9/?:().,''+ -]%' or    -- Contains no invalid characters

                @SEPAMANDATECUSTOMIDENTIFIER not like '%[a-zA-Z0-9]'                            -- Ends with alphanumeric

             )
            raiserror('BBERR_SEPAMANDATE_INVALIDCUSTOMIDENTIFIER', 13, 1);

        if @SEPAMANDATEID is null
            set @SEPAMANDATEID = newid();

        --The constituent account may be either a batch constituent account or a committed constituent account

        declare @BATCHREVENUECONSTITUENTACCOUNTID uniqueidentifier = null;
        declare @BATCHSPONSORSHIPCONSTITUENTACCOUNTID uniqueidentifier = null;

        if exists (select 1 from dbo.BATCHREVENUECONSTITUENTACCOUNT where BATCHREVENUECONSTITUENTACCOUNT.ID = @CONSTITUENTACCOUNTID)
        begin
            set @BATCHREVENUECONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID;
            set @CONSTITUENTACCOUNTID = null;
        end

        if exists(select 1 from dbo.BATCHSPONSORSHIPCONSTITUENTACCOUNT where BATCHSPONSORSHIPCONSTITUENTACCOUNT.ID = @CONSTITUENTACCOUNTID)
        begin
            set @BATCHSPONSORSHIPCONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID;
            set @CONSTITUENTACCOUNTID = null;
        end

        --Default the mandate status for mandates that were imported

        --If there is a signature, set the mandate to active.  Otherwise, leave it at pending signature.

        --0 = Pending signature

        --1 = Active

        declare @SEPAMANDATESTATUSCODE tinyint = 0;
        if @SEPAMANDATESIGNATUREDATE is not null
        begin
            set @SEPAMANDATESTATUSCODE = 1;
        end

        insert into dbo.SEPAMANDATE
        (
            ID,
            CONSTITUENTACCOUNTID,
            BATCHREVENUECONSTITUENTACCOUNTID,
            BATCHSPONSORSHIPCONSTITUENTACCOUNTID,
            CUSTOMIDENTIFIER,
            SIGNATUREDATE,
            TYPECODE,
            STATUSCODE,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        values
        (
            @SEPAMANDATEID,
            @CONSTITUENTACCOUNTID,
            @BATCHREVENUECONSTITUENTACCOUNTID,
            @BATCHSPONSORSHIPCONSTITUENTACCOUNTID,
            @SEPAMANDATECUSTOMIDENTIFIER,
            @SEPAMANDATESIGNATUREDATE,
            @SEPAMANDATETYPECODE,
            @SEPAMANDATESTATUSCODE,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        );
    end
end