B_P_M.sql

----------------------------
-- BPM matching --
----------------------------


-- Get BPM Id
-----------------------
declare @bassur_id VARCHAR2(38);
set @bassur_id = 'B7D8AF73-3A74-4A9F-9CA4-BAACB4C186AE'; -- BPM product category


-- Get all BPM product
--------------------------------
declare @BassurProduct table
(
   Id uniqueidentifier,
   ProductId VARCHAR2(38)
);
insert into @BassurProduct (ProductId)
select Id
from Products
where CategoryId = @bassur_id;


-- Get all actual product conditions
------------------------------------

declare @curr_date DATETIME;
select @curr_date = CURRENT_DATE from DUAL; -- environment variable in OracleDB

declare @curr_id VARCHAR2(38);
select @curr_id = min(Id) from @BassurProduct;

declare @BassurProductCondition table
(
    Id uniqueidentifier,
    ProductConditionId VARCHAR2(38)
);

while @curr_id is not null
begin
    update @BassurProductCondition
    set ProductConditionId = (
        -- at this point top(1) is in some sense redundant, as system already
        -- deprecates the creation from UI of two conditions that intersect on validity period    
        select top(1) Id from ProductCondition as pc 
        where pc.ProductId = @curr_id
        and pc.StartDate <= @curr_date
        and pc.EndDate >= @curr_date
    )
    where ProductId = @curr_id;
    select @curr_id = min(Id) from @BassurProduct where Id > @curr_id;
end


-- Prepare condition specification and custmomer parameters
-----------------------------------------------------------
declare @cust_age VARCHAR2(50)
-- some mechanism to retrieve params from the page
-- @c_age, @c_nationality, c_employment, c_total_assets 
exec dbo.GetParametersFromPage;

declare @spec_customer VARCHAR2(50)
set @spec_customer = '410006E1-CA4E-4502-A9EC-E54D922D2C00' -- Cutomer parameters

-- Get Customer parameters from one certain Product condition
declare @BassurProductConditionSpecification table
(
    Id uniqueidentifier,
    ProductConditionId VARCHAR2(38),
    SpecInConditionTypeId VARCHAR2(38), -- equal to '410006E1-CA4E-4502-A9EC-E54D922D2C00' -- Cutomer parameters
    SpecificationId VARCHAR2(38),
    SpecificationTitle VARCHAR(250),
    CurrentType VARCHAR2(50), -- {'single', 'range'}
    TextValue VARCHAR2(500),
    FloatValue DECIMAL(),
    MinFloatValue DECIMAL(),
    MaxFloatValue DECIMAL(),
    IntegerValue INT(),
    MinIntegerValue INT(),
    MaxIntegerValue INT(),
    BooleanValue boolean
);

-- Script Task sets customer parameters into the table
declare @BassurCustomerParams table --GlbCParamInCase
(
    Id uniqueidentifier,
    CaseId VARCHAR2(38),

    ProductCategoryId VARCHAR2(38),
    ParamTitle VARCHAR2(50),
    ParamValue VARCHAR(50),
    SpecificationId VARCHAR(38),


    CAge VARCHAR2(50),
    CAgeSpecId VARCHAR(38),
    CNationalityId VARCHAR2(38),
    CNationalitySpecId VARCHAR(38),
    CEmploymentId VARCHAR2(38),
    CEmploymentSpecId VARCHAR(38),
    CTotalAssets VARCHAR2(50),
    CTotalAssetsSpecId VARCHAR(38) 
);
insert into @BassurCustomerParams
select *
from GlbCParamInProdMatch
where CaseId = @case_id;

declare @BassurParmsComparison table
(
    Id uniqueidentifier,
    CaseId VARCHAR2(38)

)



-- Compare condition specifications with parameters
---------------------------------------------------

declare @eligible boolean;
set @eligible = 1;

declare @curr_id_1 VARCHAR2(38);
declare @curr_id_2 VARCHAR2(38);

select @curr_id_1 = min(Id) from @BassurProductCondition;
while @curr_id_1 is not null
begin


    insert into @BassurProductConditionSpecification
    select ProductConditionId, SpecInConditionTypeId, SpecificationId, SpecificationListItemId, CurrentType, TextValue, FloatValue, MinFloatValue, MaxFloatValue,
        IntegerValue, MinIntegerValue, MaxIntegerValue, BooleanValue
    from SpecInCondition
    where ProductConditionId = @prod_cond_id
    and SpecInConditoinTypeId = @spec_customer;


    insert into @BassurParmsComparison
    select *
    from @BassurProductConditionSpecification
    inner join @BassurCustomerParams
    on @BassurProductConditionSpecification.SpecificationId = @BassurCustomerParams.SpecificationId;


    select @curr_id_2 = min(Id) from @BassurParmsComparison;
    while @curr_id_2 is not null
    begin

        set @param_title =
        (
            select @BassurParmsComparison.SpecificationTitle
            from @BassurParmsComparison
            where Id = @curr_id_2
        );

        if (@parma_title = 'Age')
        begin
            set @param_value =
            (
                select ParamValue
                from @BassurParmsComparison
                where Id = @curr_id_2
            );
            set @spec_value =
            (
                select IntegerValue
                from @BassurParmsComparison
                where Id = @curr_id_2
            );

            if (@param_value != @spec_value)
            begin
                set @eligible = 0;
                break;
            end;
        end;

        if (@param_title = 'Nationality')
        begin
            set @param_value =
            (
                select ParamValue
                from @BassurParmsComparison
                where Id = @curr_id_2
            );
            set @spec_value =
            (
                select convert(VARCHAR(38), SpecificationListItemId)
                from @BassurParmsComparison
                where Id = @curr_id_2
            );

            if (@param_value in @spec_value)
            begin
                set @eligible = 0;
                break;
            end;
        end;

        select @curr_id_2 = min(Id) from @BassurParmsComparison where Id > @curr_id;
    end



    select @curr_id = min(Id) from @BassurProductConditoin where Id > @curr_id;
end














/*
DECLARE @GlbContactProdMatchingRule TABLE
(
    Id uniqueidentifier,
    ProductCategoryId VARCHAR2(38),
    SpecificationId VARCHAR2(38),
    CurrentType VARCHAR2(50) -- {'single', 'range'}
    -- ContactParameter 
);
*/