You are a helpful assistant. CREATE PROCEDURE tmpAddRule @StructId INT, @BIK…
- 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
Возможно ли как то в зависимости от правила домнажать максимальное значение франшизы на определенный процент?
- 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
@MaxFranchiseSumvalue accordingly.Here’s how you can modify your stored procedure to achieve this:
- Add a new parameter to the procedure for percentage adjustment.
- Use the value of this parameter to calculate the adjusted
@MaxFranchiseSum. - 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; GOTo call the procedure with a percentage adjustment, you can pass an additional parameter indicating the percentage. For example, to adjust the
@MaxFranchiseSumby 10%, you'd call:EXEC tmp_AddRule 5026, '044525700', 30000, 10.0;This call will increase the
@MaxFranchiseSumby 10% before processing. Adjust the percentage as needed for different rules.