DECLARE @CampaignId UNIQUEIDENTIFIER, @CampaignDiscountID UNIQUEIDENTIFIER, @CampaignDiscountCounditionId UNIQUEIDENTIFIER, @CampaignDiscountCounditionParamId UNIQUEIDENTIFIER, @CampaignName NVARCHAR(255), @ValidFrom DATETIME, @ValidThru DATETIME, @Description NVARCHAR(2000), @Priority INT, @DiscountPercent DECIMAL(25, 4), @DiscountAmount DECIMAL(25, 4), @DiscountPriority INT, @SortByPrice INT, @IsExclusive BIT, @UseQtyLimit BIT, @QtyLimit DECIMAL(25, 4), @UseAmountLimit BIT, @AmountLimit DECIMAL(25, 4), @UseTotalAmountLimit BIT, @TotalAmountLimit DECIMAL(25, 4), @UseTotalQtyLimit BIT, @TotalQtyLimit DECIMAL(25, 4), @UseNumberToDiscount BIT, @NumberToDiscount INT, @CurrencyCode CHAR(3), @MinimalOrderAmount DECIMAL(25, 4), @ClientType INT, @DiscountTransferable BIT, @ItemNumber NVARCHAR(255), @ItemDiscountPercent DECIMAL(25, 4), @ItemDiscountAmount DECIMAL(25, 4), @ItemToMap NVARCHAR(255), @ConditionHandlerClassName NVARCHAR(512); DECLARE @DiscountPerCurrency table (curecny_code nvarchar(max), discount_percent decimal(25,4), discount_amount decimal(25,4)) DECLARE @Items table (item nvarchar(max)); -- Campaign parameters SET @CampaignName = 'Test' SET @ValidFrom = '2015-01-14 11:13:00.000' SET @ValidThru = '2015-04-30 11:13:00.000' SET @Description = 'My Desc' SET @Priority = 1 -- List of items INSERT INTO @Items VALUES ('DMN-COM') INSERT INTO @Items VALUES ('DMN-ORG') INSERT INTO @Items VALUES ('DMN-NET') -- List of currencies INSERT INTO @DiscountPerCurrency VALUES ('USD', 20.0000, 0.0000) INSERT INTO @DiscountPerCurrency VALUES ('SEK', 20.0000, 0.0000) BEGIN TRAN SET @CampaignId = NEWID() INSERT INTO campaign (id, name, valid_from, valid_thru, [description], is_deleted, [priority]) VALUES (@CampaignId, @CampaignName, @ValidFrom, @ValidThru, @Description, 0, @Priority) -- Set campaign conditions SET @DiscountPriority = 1 SET @SortByPrice = 1 SET @IsExclusive = 1 SET @UseQtyLimit = 0 SET @QtyLimit = 0.0000 SET @UseAmountLimit = 0 SET @AmountLimit = 0.0000 SET @UseTotalAmountLimit = 0 SET @TotalAmountLimit = 0.0000 SET @UseTotalQtyLimit = 0 SET @TotalQtyLimit = 0.0000 SET @UseNumberToDiscount = 1 --check SET @NumberToDiscount = 1000 --check SET @MinimalOrderAmount = 0.0000 SET @ClientType = 11 --fix value and move down SET @DiscountTransferable = 0 DECLARE curency_cursor CURSOR FOR SELECT * FROM @DiscountPerCurrency OPEN curency_cursor FETCH NEXT FROM curency_cursor into @CurrencyCode, @DiscountPercent, @DiscountAmount WHILE @@FETCH_STATUS = 0 BEGIN SET @CampaignDiscountID = NEWID() INSERT INTO campaign_discount (id, fk_campaign_id, discount_percent, discount_amount, [priority], sort_by_price, is_exclusive, use_qty_limit, qty_limit, use_amount_limit, amount_limit, use_total_amount_limit, total_amount_limit, use_total_qty_limit, total_qty_limit, use_number_to_discount, number_to_discount, currency_code, minimal_order_amount, client_type, discount_transferable) VALUES (@CampaignDiscountID, @CampaignId, @DiscountPercent, @DiscountAmount, @DiscountPriority, @SortByPrice, @IsExclusive, @UseQtyLimit, @QtyLimit, @UseAmountLimit, @AmountLimit, @UseTotalAmountLimit, @TotalAmountLimit, @UseTotalQtyLimit, @TotalQtyLimit, @UseNumberToDiscount, @NumberToDiscount, @CurrencyCode, @MinimalOrderAmount, @ClientType, @DiscountTransferable) /****** Dicounted items ******/ -- Insert into campaign_discount_item SET @ItemDiscountPercent = 0.0000 SET @ItemDiscountAmount = 0.0000 SET @ItemToMap = '' SET @ConditionHandlerClassName = '' INSERT INTO campaign_discount_item (id, fk_campaign_discount_id, item_number, discount_percent, discount_amount, item_to_map, condition_handler_class_name, currency_code) SELECT NEWID(), @CampaignDiscountID, item, @ItemDiscountPercent, @ItemDiscountAmount, @ItemToMap, @ConditionHandlerClassName, @CurrencyCode FROM @Items /******** ContainsDomainNameWithDiffTldConditionHandler ********/ -- Insert campaign_discount_condition SET @CampaignDiscountCounditionId = NEWID() INSERT INTO campaign_discount_condition (id, fk_campaign_discount_id, condition_handler) VALUES(@CampaignDiscountCounditionId, @CampaignDiscountID, 'ContainsDomainNameWithDiffTldConditionHandler') -- Insert campaign_discount_condition_param SET @CampaignDiscountCounditionParamId = NEWID() INSERT INTO campaign_discount_condition_param (id, fk_campaign_discount_condition_id, name, value) VALUES(@CampaignDiscountCounditionParamId, @CampaignDiscountCounditionId, 'settings', '') /******** OrderContainCustomAttributeConditionHandler ********/ -- Insert campaign_discount_condition SET @CampaignDiscountCounditionId = NEWID() INSERT INTO campaign_discount_condition (id, fk_campaign_discount_id, condition_handler) VALUES(@CampaignDiscountCounditionId, @CampaignDiscountID, 'OrderContainCustomAttributeConditionHandler') -- Insert campaign_discount_condition_param SET @CampaignDiscountCounditionParamId = NEWID() INSERT INTO campaign_discount_condition_param (id, fk_campaign_discount_condition_id, name, value) VALUES(@CampaignDiscountCounditionParamId, @CampaignDiscountCounditionId, 'settings', '{"ContainsKeyName": "DomainName", "DoesntContainKeyName": ""}') /******** CustomerHasDefaultDomainContactConditionHandler ********/ -- Insert campaign_discount_condition SET @CampaignDiscountCounditionId = NEWID() INSERT INTO campaign_discount_condition (id, fk_campaign_discount_id, condition_handler) VALUES(@CampaignDiscountCounditionId, @CampaignDiscountID, 'CustomerHasDefaultDomainContactConditionHandler') -- Insert campaign_discount_condition_param SET @CampaignDiscountCounditionParamId = NEWID() INSERT INTO campaign_discount_condition_param (id, fk_campaign_discount_condition_id, name, value) VALUES(@CampaignDiscountCounditionParamId, @CampaignDiscountCounditionId, 'settings', '{"PassIfFound": "true"}') /******** CustomerHasDomainOfTldAndContactConditionHandler ********/ -- Insert campaign_discount_condition SET @CampaignDiscountCounditionId = NEWID() INSERT INTO campaign_discount_condition (id, fk_campaign_discount_id, condition_handler) VALUES(@CampaignDiscountCounditionId, @CampaignDiscountID, 'CustomerHasDomainOfTldAndContactConditionHandler') -- Insert campaign_discount_condition_param SET @CampaignDiscountCounditionParamId = NEWID() INSERT INTO campaign_discount_condition_param (id, fk_campaign_discount_condition_id, name, value) VALUES(@CampaignDiscountCounditionParamId, @CampaignDiscountCounditionId, 'settings', '') /******** CustomerHasDomainWithDiffTldConditionHandler ********/ -- Insert campaign_discount_condition SET @CampaignDiscountCounditionId = NEWID() INSERT INTO campaign_discount_condition (id, fk_campaign_discount_id, condition_handler) VALUES(@CampaignDiscountCounditionId, @CampaignDiscountID, 'CustomerHasDomainWithDiffTldConditionHandler') -- Insert campaign_discount_condition_param SET @CampaignDiscountCounditionParamId = NEWID() INSERT INTO campaign_discount_condition_param (id, fk_campaign_discount_condition_id, name, value) VALUES(@CampaignDiscountCounditionParamId, @CampaignDiscountCounditionId, 'settings', '{"StartDate": "", "EndDate": "", "IsCurrentMonth": "true"}') FETCH NEXT FROM curency_cursor into @CurrencyCode, @DiscountPercent, @DiscountAmount END CLOSE curency_cursor DEALLOCATE curency_cursor COMMIT TRAN