UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED

CREATE function dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED  
(  
    @INSTALLEDPRODUCTLIST xml  
)  
returns bit  
with execute as caller  
as begin  

    /*  
    NOTE: This function does not check that the INSTALLATIONINFO.INSTALLEDPRODUCTS hash is valid.  
    It is up to the caller to make that check if it is necessary.  
    */  

    if 0 = coalesce(@INSTALLEDPRODUCTLIST.exist  
    (  
        'declare namespace common="bb_appfx_commontypes";  
        /common:InstalledProductList/common:InstalledProduct/@ID'  
    ), 0)  
        return 1;  
    else  
    begin  
    if (@INSTALLEDPRODUCTLIST.value('/*[1]/@EvaluationMethod', 'nvarchar(5)') = N'All')  
    begin  
        --All products must be installed  

        if not exists  
        (  
            select  
            1
            from  
            @INSTALLEDPRODUCTLIST.nodes  
            (  
                'declare namespace common="bb_appfx_commontypes";  
                /InstalledProductList/InstalledProduct'  
            ) T(c)  
            left join dbo.INSTALLEDPRODUCTLIST on 
                (T.c.value('@ID', 'uniqueidentifier') = INSTALLEDPRODUCTLIST.ID)         
            where
            --The product flag has not expired does exist and the spec does not want it to exist

            (
                (  
                    len(INSTALLEDPRODUCTLIST.EXPIREDATE) = 0  
                    or  
                    getdate() <= convert(datetime, substring(INSTALLEDPRODUCTLIST.EXPIREDATE,1,4) + '-' + substring(INSTALLEDPRODUCTLIST.EXPIREDATE,5,2) + '-' + substring(INSTALLEDPRODUCTLIST.EXPIREDATE,7,2) + 'T00:00:00')   
                ) 
                and  
                (
                    INSTALLEDPRODUCTLIST.ID is not null
                )
                and
                (
                    T.c.value('@EnforceNotInstalled', 'bit') = 1
                )
            )
            or
            --The product flag has expired and the spec does want it to exist

            (
                (
                    len(INSTALLEDPRODUCTLIST.EXPIREDATE) > 0
                    and
                    getdate() >= convert(datetime, substring(INSTALLEDPRODUCTLIST.EXPIREDATE,1,4) + '-' + substring(INSTALLEDPRODUCTLIST.EXPIREDATE,5,2) + '-' + substring(INSTALLEDPRODUCTLIST.EXPIREDATE,7,2) + 'T00:00:00')
                )
                and
                (
                    T.c.value('@EnforceNotInstalled', 'bit') IS NULL
                    or 
                    T.c.value('@EnforceNotInstalled', 'bit') = 0
                )
            ) 
            or
            --The product flag does not exist and the spec does want it to exist

            (
                (
                    not exists (select 1 from INSTALLEDPRODUCTLIST where T.c.value('@ID', 'uniqueidentifier') = INSTALLEDPRODUCTLIST.ID)
                )
                and
                (
                    T.c.value('@EnforceNotInstalled', 'bit') IS NULL
                    or 
                    T.c.value('@EnforceNotInstalled', 'bit') = 0
                )  
            )
    )
            return 1;  
    end  
    else  
    begin  
     --Any product must be installed  

        if exists  
        (  
            select  
            1
            from  
            @INSTALLEDPRODUCTLIST.nodes  
            (  
                'declare namespace common="bb_appfx_commontypes";  
                /common:InstalledProductList/common:InstalledProduct'  
            ) T(c)  
            left join dbo.INSTALLEDPRODUCTLIST on 
                (T.c.value('@ID', 'uniqueidentifier') = INSTALLEDPRODUCTLIST.ID)         
            where
            --The product flag has not expired does exist and the spec wants it to exist

            (
                (  
                    len(INSTALLEDPRODUCTLIST.EXPIREDATE) = 0  
                    or  
                    getdate() <= convert(datetime, substring(INSTALLEDPRODUCTLIST.EXPIREDATE,1,4) + '-' + substring(INSTALLEDPRODUCTLIST.EXPIREDATE,5,2) + '-' + substring(INSTALLEDPRODUCTLIST.EXPIREDATE,7,2) + 'T00:00:00')   
                ) 
                and  
                (
                    INSTALLEDPRODUCTLIST.ID is not null
                )
                and
                (
                    T.c.value('@EnforceNotInstalled', 'bit') IS NULL
                    or 
                    T.c.value('@EnforceNotInstalled', 'bit') = 0
                )
            )
            or
            --The product flag has expired and the spec does not want it to exist

            (
                (
                    len(INSTALLEDPRODUCTLIST.EXPIREDATE) > 0
                    and
                    getdate() >= convert(datetime, substring(INSTALLEDPRODUCTLIST.EXPIREDATE,1,4) + '-' + substring(INSTALLEDPRODUCTLIST.EXPIREDATE,5,2) + '-' + substring(INSTALLEDPRODUCTLIST.EXPIREDATE,7,2) + 'T00:00:00')
                )
                and
                (
                    T.c.value('@EnforceNotInstalled', 'bit') = 1
                )
            ) 
            or
            --The product flag does not exist and the spec does not want it to exist

            (
                (
                    not exists (select 1 from INSTALLEDPRODUCTLIST where T.c.value('@ID', 'uniqueidentifier') = INSTALLEDPRODUCTLIST.ID)
                )
                and
                (
                    T.c.value('@EnforceNotInstalled', 'bit') = 1
                )  
            )
        )
            return 1;  
        end  
    end  

    return 0;  
end