SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].al_split_block
    @start_node int,
    @block_id int
AS
BEGIN
  
    SET NOCOUNT ON;

    
    declare @from int
    declare @to int
    declare @licblock int
    declare @prodblock int
    declare @distance int
    declare @count int
    declare @new_block_id int
    declare @al_temp_lic_list table
    (
        node_id int not null,
        group_id int not null,
        predecessor int null,
        done bit not null
    )
    

    declare @al_temp_lic_prod_link table
    (
        license_node_id int not null,
        product_node_id int not null,
        distance int not null,
        lic_blk_table_id int not null,
        prod_blk_table_id int not null        
    )

    --4/16/08 DD To optimize the running of the split procedure, we do not need to split if there is only
    --           one product associated with a block.  There may be other optimizations eventually.  If so
    --           they should go here.

    declare @prodcount int

    select @prodcount = count(*) from al_link_product_block where block_id = @block_id;
    if @prodcount = 1
    begin
        update al_license_block set evaluate=1, last_update_user = 'PROC:SKIP SPLIT' where block_id = @block_id
        return
    end
        

    -- Join brings together the license block, product block tables and assigns a unique node id to each product and license
    -- for evaluation through a shortest path algorithm in order to determine if a block was split.
    insert into @al_temp_lic_list (node_id, group_id, predecessor, done)
    SELECT ROW_NUMBER() over (order by license_block_union.block_tbl_id) as node_id, 1, NULL, 0
        FROM
            (
                select   link_lm_block_id as block_tbl_id, 'L' as objtype , license_id ,license_metric_id,  NULL as product_uuid
                from al_link_lic_metric_block
                where block_id = @block_id

                    union

                select  link_product_block_id as block_tbl_id, 'P' as objtype , NULL as license_id, null as license_metric_id, product_uuid
                from al_link_product_block
                where block_id = @block_id
            ) license_block_union
    order by objtype

    

    -- Join brings together the license block, product block, and product rights tables with the unique node id to determine all the connections between
    -- licenses and products.  If any link (edge) is a cut edge (an edge that if removed products a subgraph with more connected components than the orginal graph.)
    -- the alogrithm will be used to segment or split the block of licenses and products into two groups.
    DECLARE license_block_cursor CURSOR FOR
SELECT  rights_block.node_id as license_node_id, block_union3.product_node_id, 0 as distance, block_tbl_id as license_blk_tbl_id, block_union3.product_blk_tabl_id from
        (
        SELECT ROW_NUMBER() over (order by license_block_union.block_tbl_id) as node_id, objtype, block_tbl_id, license_block_union.license_id, license_block_union.license_metric_id, license_block_union.product_uuid
        FROM
            (
                select   link_lm_block_id as block_tbl_id, 'L' as objtype , license_id ,license_metric_id,  NULL as product_uuid
                from al_link_lic_metric_block
                where block_id = @block_id

                    union

                select  link_product_block_id as block_tbl_id, 'P' as objtype , NULL as license_id, null as license_metric_id, product_uuid
                from al_link_product_block
                where block_id = @block_id
            ) license_block_union
        )rights_block  LEFT OUTER JOIN al_product_rights pr on pr.license_id = rights_block.license_id  

left outer join
        (SELECT ROW_NUMBER() over (order by block_union2.block_tbl_id) as product_node_id, block_tbl_id as product_blk_tabl_id, product_uuid
        FROM

            (
                select   link_lm_block_id as block_tbl_id, 'L' as objtype , license_id , NULL as product_uuid
                from al_link_lic_metric_block
                where block_id = @block_id

                    union

                select  link_product_block_id as block_tbl_id, 'P' as objtype , NULL as license_id, product_uuid
                from al_link_product_block
                where block_id = @block_id
            ) block_union2 ) block_union3 on pr.product_uuid = block_union3.product_uuid
    where objtype='L' and rights_block.license_metric_id is null


union

SELECT  rights_block.node_id as license_node_id, block_union3.product_node_id, 0 as distance, block_tbl_id as license_blk_tbl_id, block_union3.product_blk_tabl_id from
        (
        SELECT ROW_NUMBER() over (order by license_block_union.block_tbl_id) as node_id, objtype, block_tbl_id, license_block_union.license_id, license_block_union.license_metric_id, license_block_union.product_uuid
        FROM
            (
                select   link_lm_block_id as block_tbl_id, 'L' as objtype , license_id ,license_metric_id,  NULL as product_uuid
                from al_link_lic_metric_block
                where block_id = @block_id

                    union

                select  link_product_block_id as block_tbl_id, 'P' as objtype , NULL as license_id, null as license_metric_id, product_uuid
                from al_link_product_block
                where block_id = @block_id
            ) license_block_union
        )rights_block  
                                
                                left join al_metric_covered_product cp on rights_block.license_metric_id  = cp.license_metric_id
                                left join al_product_rights pr on cp.product_rights_id = pr.product_rights_id                                                            
left outer join  
        (SELECT ROW_NUMBER() over (order by block_union2.block_tbl_id) as product_node_id, block_tbl_id as product_blk_tabl_id, product_uuid
        FROM

            (
                select   link_lm_block_id as block_tbl_id, 'L' as objtype , license_id , NULL as product_uuid
                from al_link_lic_metric_block
                where block_id = @block_id

                    union

                select  link_product_block_id as block_tbl_id, 'P' as objtype , NULL as license_id, product_uuid
                from al_link_product_block
                where block_id = @block_id
            ) block_union2 ) block_union3 on pr.product_uuid = block_union3.product_uuid

    where objtype='L' and rights_block.license_metric_id is not null
    




    OPEN license_block_cursor

    -- Perform the first fetch.
    FETCH NEXT FROM license_block_cursor
    INTO @from, @to, @distance, @licblock, @prodblock

    -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
    WHILE @@FETCH_STATUS = 0
    BEGIN
       -- This is executed as long as the previous fetch succeeds.
        insert into @al_temp_lic_prod_link(license_node_id, product_node_id, distance, lic_blk_table_id, prod_blk_table_id)
                                         values(@from, @to, @distance, @licblock, @prodblock)
        insert into @al_temp_lic_prod_link(license_node_id, product_node_id, distance, lic_blk_table_id, prod_blk_table_id)
                                         values(@to, @from, @distance,  @licblock, @prodblock)
       FETCH NEXT FROM license_block_cursor
        INTO @from, @to, @distance, @licblock, @prodblock

    END

    CLOSE license_block_cursor
    DEALLOCATE license_block_cursor
    

    update @al_temp_lic_list set group_id = 0 where node_id = @start_node

    IF @@rowcount <> 1
    BEGIN
        RAISERROR ('Couldn''t set start node', 11, 1)
        ROLLBACK TRAN        
        RETURN
    END

    DECLARE @from_node Int, @current_group_id Int

    -- Run the algorithm until we decide that we are finished
    WHILE 1=1
    BEGIN
        -- Reset the variable, so we can detect getting no records in the next step.
        SELECT @from_node = NULL

        -- Select the node id and current estimate for the node not done, with the lowest estimate.
        SELECT TOP 1 @from_node = node_id, @current_group_id = group_id
        FROM @al_temp_lic_list WHERE done = 0 AND group_id < 1
        ORDER BY group_id
        
        -- Stop if we have no more unvisited, reachable node.
        IF @from_node IS NULL BREAK

        -- We are now done with this node.
        UPDATE @al_temp_lic_list SET done = 1 WHERE node_id = @from_node

        -- Update the estimates to all neighbor nodes of this one (all the nodes
        -- there are links to from this node). Only update the estimate if the new
        -- proposal (to go via the current node) is better (lower).
        UPDATE @al_temp_lic_list SET group_id = @current_group_id + distance,
            predecessor = @from_node
        FROM @al_temp_lic_list INNER JOIN @al_temp_lic_prod_link ON node_id = license_node_id
        WHERE product_node_id = @from_node AND (@current_group_id + distance) < group_id
        
    END
    
    
    --Do we have two groups of licenses and products?
    select @count = count(*) from @al_temp_lic_list  where group_id > 0
    
    if @count > 0
        begin
            exec  @new_block_id  = al_get_next_block_id
            -- Update all licenses that have been split with new block number
            
            update al_link_lic_metric_block set block_id = @new_block_id, last_update_user = 'PROC:U, SPLIT' where link_lm_block_id in
                (SELECT distinct lic_blk_table_id
                FROM @al_temp_lic_list, @al_temp_lic_prod_link
                where node_id = license_node_id and group_id = 1)
            --Update all products that have been split with new block number
            update al_link_product_block set block_id = @new_block_id, last_update_user = 'PROC:U, SPLIT' where link_product_block_id in
                (SELECT distinct prod_blk_table_id
                FROM @al_temp_lic_list, @al_temp_lic_prod_link
                where node_id = product_node_id and group_id = 1)
            -- create new block number.
            insert into al_license_block (block_id, finite, evaluate, last_update_user) values(@new_block_id,1, 1 , 'PROC:C, SPLIT')

            --update the orginal block number.
            update al_license_block set evaluate=1, last_update_user = 'PROC:U, SPLIT' where block_id = @block_id

        end
/* 10/18/07 DD Problem where blocks that do not split do not update evaluate to 1
               This problem was reported as an issue by Khris Cole and researched
               by Jimmy Wu.  How this is reproduced is you have two licenses both covering
               the same product and one license is removed from the block for some reason
               (i.e. inactivated, terminated, deleted, etc.) When the one license leaves
               the block, this split algorithm is run.  If there is a split the above code
               executes.  Notice the update to license block happens in that code.  However,
               this case does not cause a split of a block, but the block, nonetheless needs to
               be re-evaluated.  Since the split is always checked on an unlink we need
               to always set re-evaluate regardless of what happens to the block (splits into
               two blocks or does not.)
*/
    else
        update al_license_block set evaluate=1, last_update_user = 'PROC:U, SPLIT' where block_id = @block_id

END
GO
GRANT EXECUTE ON  [dbo].[al_split_block] TO [swcmadmin]
GRANT EXECUTE ON  [dbo].[al_split_block] TO [uapmadmin_group]
GRANT EXECUTE ON  [dbo].[al_split_block] TO [uapmbatch_group]
GRANT EXECUTE ON  [dbo].[al_split_block] TO [uapmreporting_group]
GO