Skip to content
TheChatArchiveThe Chat Archive
gpt-4o-2024-08-06· OpenAIAi2 WildChat-4.8M (AI2)

You are a helpful assistant. CREATE PROCEDURE tmpAddRule @StructId INT, @BIK…

  1. User

    You are a helpful assistant. CREATE PROCEDURE tmp_AddRule @StructId INT, @BIK NVARCHAR(20), @MaxFranchiseSum INT AS BEGIN SET NOCOUNT ON;

    DECLARE @Product_ID INT;
    DECLARE @Rule_Id INT;
    
    SELECT TOP 1 @Product_ID = Id
    FROM Product WITH (NOLOCK)
    WHERE ProductDictCode = 'KASKO' AND State = 2 AND IsDeleted = 0;
    
    SELECT @Rule_Id = Id 
    FROM [Rule] WITH (NOLOCK)
    WHERE Condition = CONCAT('["', @BIK, '"].indexOf(Deal.SuggestedBanks[0].BIK) >= 0') 
    AND Product_Id = @Product_ID;
    
    IF NOT EXISTS (
        SELECT 1 FROM [Rule] WITH (NOLOCK)
        WHERE Condition = CONCAT('["', @BIK, '"].indexOf(Deal.SuggestedBanks[0].BIK) >= 0') 
        AND Product_Id = @Product_ID
    )
    BEGIN
        INSERT INTO [Rule] (Condition, EntityId, AreaId, CreateDate, State, DictionaryDate, IsDeleted, Product_Id, InitializationType)
        VALUES (
            CONCAT('["', @BIK, '"].indexOf(Deal.SuggestedBanks[0].BIK) >= 0'), 
            NEXT VALUE FOR SQ_Rule, 
            1, 
            GETDATE(), 
            2, 
            GETDATE(), 
            0, 
            @Product_ID, 
            'FirstInitialization'
        );
    
        SELECT @Rule_Id = SCOPE_IDENTITY();
    END;
    
    -- SetSumSetting
    IF NOT EXISTS (
        SELECT 1 FROM SetSumSetting WITH (NOLOCK)
        WHERE StructId = @StructId AND Rule_Id = @Rule_Id
    )
    BEGIN
        INSERT INTO SetSumSetting (StructId, RangeStart, RangeEnd, IsInclude, DefaultSum, EntityId, AreaId, CreateDate, State, DictionaryDate, IsDeleted, Rule_Id)
        VALUES (
            @StructId, 
            0, 
            @MaxFranchiseSum,
            1, 
            10000, 
            NEXT VALUE FOR SQ_SetSumSetting, 
            1, 
            GETDATE(), 
            2, 
            GETDATE(), 
            0, 
            @Rule_Id
        );
    END;
    

    END; GO

    -- FranType = 1 EXEC tmp_AddRule 5026, '044525700', 30000; EXEC tmp_AddRule 5027, '044525700', 30000; EXEC tmp_AddRule 5028, '044525700', 30000;

    EXEC tmp_AddRule 5026, '044525545', 50000; EXEC tmp_AddRule 5027, '044525545', 50000; EXEC tmp_AddRule 5028, '044525545', 50000;

    EXEC tmp_AddRule 5026, '044525159', 50000; EXEC tmp_AddRule 5027, '044525159', 50000; EXEC tmp_AddRule 5028, '044525159', 50000;

    EXEC tmp_AddRule 5026, '044525767', 100000; EXEC tmp_AddRule 5027, '044525767', 100000; EXEC tmp_AddRule 5028, '044525767', 100000;

    EXEC tmp_AddRule 5026, '044525630', 75000; EXEC tmp_AddRule 5027, '044525630', 75000; EXEC tmp_AddRule 5028, '044525630', 75000;

    EXEC tmp_AddRule 5026, '044525161', 30000; EXEC tmp_AddRule 5027, '044525161', 30000; EXEC tmp_AddRule 5028, '044525161', 30000;

    EXEC tmp_AddRule 5026, '044525097', 37500; EXEC tmp_AddRule 5027, '044525097', 37500; EXEC tmp_AddRule 5028, '044525097', 37500;

    EXEC tmp_AddRule 5026, '044525811', 60000; EXEC tmp_AddRule 5027, '044525811', 60000; EXEC tmp_AddRule 5028, '044525811', 60000;

    EXEC tmp_AddRule 5026, '044525659', 50000; EXEC tmp_AddRule 5027, '044525659', 50000; EXEC tmp_AddRule 5028, '044525659', 50000;

    EXEC tmp_AddRule 5026, '044525339', 30000; EXEC tmp_AddRule 5027, '044525339', 30000; EXEC tmp_AddRule 5028, '044525339', 30000;

    EXEC tmp_AddRule 5026, '044030790', 50000; EXEC tmp_AddRule 5027, '044030790', 50000; EXEC tmp_AddRule 5028, '044030790', 50000;

    EXEC tmp_AddRule 5026, '049205815', 50000; EXEC tmp_AddRule 5027, '049205815', 50000; EXEC tmp_AddRule 5028, '049205815', 50000;

    EXEC tmp_AddRule 5026, '044525619', 30000; EXEC tmp_AddRule 5027, '044525619', 30000; EXEC tmp_AddRule 5028, '044525619', 30000;

    EXEC tmp_AddRule 5026, '044525990', 15000; EXEC tmp_AddRule 5027, '044525990', 15000; EXEC tmp_AddRule 5028, '044525990', 15000;

    EXEC tmp_AddRule 5026, '044525848', 50000; EXEC tmp_AddRule 5027, '044525848', 50000; EXEC tmp_AddRule 5028, '044525848', 50000;

    EXEC tmp_AddRule 5026, '044030904', 50000; EXEC tmp_AddRule 5027, '044030904', 50000; EXEC tmp_AddRule 5028, '044030904', 50000;

    EXEC tmp_AddRule 5026, '044525187', 50000; EXEC tmp_AddRule 5027, '044525187', 50000; EXEC tmp_AddRule 5028, '044525187', 50000;

    EXEC tmp_AddRule 5026, '044525985', 75000; EXEC tmp_AddRule 5027, '044525985', 75000; EXEC tmp_AddRule 5028, '044525985', 75000;

    EXEC tmp_AddRule 5026, '044525974', 50001; EXEC tmp_AddRule 5027, '044525974', 50001; EXEC tmp_AddRule 5028, '044525974', 50001;

    EXEC tmp_AddRule 5026, '044030705', 50001; EXEC tmp_AddRule 5027, '044030705', 50001; EXEC tmp_AddRule 5028, '044030705', 50001;

    EXEC tmp_AddRule 5026, '040507803', 50000; EXEC tmp_AddRule 5027, '040507803', 50000; EXEC tmp_AddRule 5028, '040507803', 50000;

    EXEC tmp_AddRule 5026, '044525460', 50000; EXEC tmp_AddRule 5027, '044525460', 50000; EXEC tmp_AddRule 5028, '044525460', 50000;

    -- FranType = NULL EXEC tmp_AddRule 5026, '044525225', 30000; EXEC tmp_AddRule 5027, '044525225', 30000; EXEC tmp_AddRule 5028, '044525225', 30000;

    EXEC tmp_AddRule 5026, '043510607', 50000; EXEC tmp_AddRule 5027, '043510607', 50000; EXEC tmp_AddRule 5028, '043510607', 50000;

    EXEC tmp_AddRule 5026, '044525185', 30000; EXEC tmp_AddRule 5027, '044525185', 30000; EXEC tmp_AddRule 5028, '044525185', 30000;

    DROP PROCEDURE tmp_AddRule; GO

    Возможно ли как то в зависимости от правила домнажать максимальное значение франшизы на определенный процент?

  2. Assistant

    Yes, it is possible to multiply the maximum franchise sum by a certain percentage based on a specific rule within your stored procedure. You can introduce an additional parameter to your procedure that accepts this percentage and use it to adjust the @MaxFranchiseSum value accordingly.

    Here’s how you can modify your stored procedure to achieve this:

    1. Add a new parameter to the procedure for percentage adjustment.
    2. Use the value of this parameter to calculate the adjusted @MaxFranchiseSum.
    3. Ensure the adjusted maximum sum is used where applicable in the procedure.

    Here's the updated version of your stored procedure:

    CREATE PROCEDURE tmp_AddRule
        @StructId INT,
        @BIK NVARCHAR(20),
        @MaxFranchiseSum INT,
        @PercentageAdjustment FLOAT = 0 -- New parameter with default value 0
    AS
    BEGIN
        SET NOCOUNT ON;
    
        DECLARE @Product_ID INT;
        DECLARE @Rule_Id INT;
    
        -- Calculate the adjusted MaxFranchiseSum based on the percentage
        SET @MaxFranchiseSum = @MaxFranchiseSum + ROUND(@MaxFranchiseSum * @PercentageAdjustment / 100.0, 0);
    
        SELECT TOP 1 @Product_ID = Id
        FROM Product WITH (NOLOCK)
        WHERE ProductDictCode = 'KASKO' AND State = 2 AND IsDeleted = 0;
    
        SELECT @Rule_Id = Id 
        FROM [Rule] WITH (NOLOCK)
        WHERE Condition = CONCAT('["', @BIK, '"].indexOf(Deal.SuggestedBanks[0].BIK) >= 0') 
        AND Product_Id = @Product_ID;
        
        IF NOT EXISTS (
            SELECT 1 FROM [Rule] WITH (NOLOCK)
            WHERE Condition = CONCAT('["', @BIK, '"].indexOf(Deal.SuggestedBanks[0].BIK) >= 0') 
            AND Product_Id = @Product_ID
        )
        BEGIN
            INSERT INTO [Rule] (Condition, EntityId, AreaId, CreateDate, State, DictionaryDate, IsDeleted, Product_Id, InitializationType)
            VALUES (
                CONCAT('["', @BIK, '"].indexOf(Deal.SuggestedBanks[0].BIK) >= 0'), 
                NEXT VALUE FOR SQ_Rule, 
                1, 
                GETDATE(), 
                2, 
                GETDATE(), 
                0, 
                @Product_ID, 
                'FirstInitialization'
            );
    
            SELECT @Rule_Id = SCOPE_IDENTITY();
        END;
    
        -- SetSumSetting
        IF NOT EXISTS (
            SELECT 1 FROM SetSumSetting WITH (NOLOCK)
            WHERE StructId = @StructId AND Rule_Id = @Rule_Id
        )
        BEGIN
            INSERT INTO SetSumSetting (StructId, RangeStart, RangeEnd, IsInclude, DefaultSum, EntityId, AreaId, CreateDate, State, DictionaryDate, IsDeleted, Rule_Id)
            VALUES (
                @StructId, 
                0, 
                @MaxFranchiseSum, -- Use the adjusted MaxFranchiseSum
                1, 
                10000, 
                NEXT VALUE FOR SQ_SetSumSetting, 
                1, 
                GETDATE(), 
                2, 
                GETDATE(), 
                0, 
                @Rule_Id
            );
        END;
    END;
    GO
    

    To call the procedure with a percentage adjustment, you can pass an additional parameter indicating the percentage. For example, to adjust the @MaxFranchiseSum by 10%, you'd call:

    EXEC tmp_AddRule 5026, '044525700', 30000, 10.0;
    

    This call will increase the @MaxFranchiseSum by 10% before processing. Adjust the percentage as needed for different rules.