--upgrade scripts from nopCommerce 3.20 to 3.30 --new locale resources declare @resources xml --a resource will be deleted if its value is empty set @resources=' Imported products are distinguished by SKU. If the SKU already exists, then its corresponding product will be updated. Invoice footer text (left column) Enter the text that will appear at the bottom of generated invoices (left column). Invoice footer text (right column) Enter the text that will appear at the bottom of generated invoices (right column). Delete all All queued emails have been deleted successfully. Stores Limited to stores Determines whether the attribute is available only at certain stores. Stores Select stores for which the attribute will be shown. Search engine friendly page name Set a search engine friendly page name e.g. ''some-topic-name'' to make your page URL ''http://www.yourStore.com/some-topic-name''. Leave empty to generate it automatically based on the title of the topic. The order associated to this shipment. Allow only existing attribute combinations Check to allow adding to the cart/wishlist only attribute combinations that exist and have stock greater than zero. In this case you have to create all existing product attribute combinations that you have in stock. Stores Limited to stores Determines whether the country is available only at certain stores. Stores Select stores for which the country will be shown. Automatically detect language Check to automatically detect language based on a customer browser settings. Purchased with product A customer is added to this customer role once a specified product is purchased (paid). Please note that in case of refund or order cancellation you have to manually remove a customer from this role. Choose product Remove You cannot specify "Purchased with product" value for "Registered" customer role OK Cookies help us deliver our services. By using our services, you agree to our use of cookies. Learn more Server variables A list of server variables Is primary dimension A value indicating whether to display this order note to a customer. Enter this order note message. Attached file Upload a file attached to this order note. Download No file attached Download attached file Validation rules View/Edit rules Minimum length Specify minimum length. Leave empty to skip this validation. Maximum length Specify maximum length. Leave empty to skip this validation. {0} : minimum length is {1} chars {0} : maximum length is {1} chars Minimum length Specify minimum length. Leave empty to skip this validation. Maximum length Specify maximum length. Leave empty to skip this validation. Processing dropped files... Delete Retry Upload a file Allowed file extensions Specify a comma-separated list of allowed file extensions. Leave empty to allow any file extension. Maximum file size (KB) Specify maximum file size in kilobytes. Leave empty to skip this validation. Allowed file extensions Specify a comma-separated list of allowed file extensions. Leave empty to allow any file extension. Maximum file size (KB) Specify maximum file size in kilobytes. Leave empty to skip this validation. Disable "Billing address" step Check to disable "Billing address" step during checkout. Billing address will be pre-filled and saved using the default registration data (this option cannot be used with guest checkout enabled). Also ensure that appropriate address fields that cannot be pre-filled are not required (or disabled). Pass dimensions Check if you want to pass package dimensions when requesting rates. Packing type Choose preferred packing type. Pack by dimensions Pack by one item per package Pack by volume Package volume Enter your package volume. Tracing Check if you want to record plugin tracing in System Log. Warning: The entire request and response XML will be logged (including AccessKey/UserName,Password). Do not leave this enabled in a production environment. To find text or a specific setting (by name), you can apply a filter via the funnel icon in the "Value" or "Setting name" column headers. To find text or a specific resource (by name), you can apply a filter via the funnel icon in the "Value" or "Resource name" column headers. Custom customer attributes Add a new customer attribute back to customer attribute list If the default form fields are not enough for your needs, then you can manage additional customer attributes below. Edit customer attribute details Attribute info The new attribute has been added successfully. The attribute has been deleted successfully. The attribute has been updated successfully. Name Please provide a name. The name of the customer attribute. Required When an attribute is required, the customer must choose an appropriate attribute value before they can continue. Control type Choose how to display your attribute values. Display order The customer attribute display order. 1 represents the first item in the list. Attribute values Add a new customer value Edit customer value details You need to save the customer attribute before you can add values for this customer attribute page. Name Please provide a name. The name of the customer value. Pre-selected Determines whether this attribute value is pre selected for the customer. Display order The display order of the attribute value. 1 represents the first item in attribute value list. Responsive design supported Check to enable responsive design. Also note that your graphical theme should also support it. No shipments selected Set as delivered (selected) Set as shipped (selected) Force tax exclusion from order subtotal Check to always exclude tax from order subtotal (no matter of selected tax dispay type). This setting effects only pages where order totals are displayed. Display tax/shipping info (footer) Check to display tax and shipping info in the footer. This option is used in Germany. Display tax/shipping info (product details page) Check to display tax and shipping info on product details pages. This option is used in Germany. Display tax/shipping info (product boxes) Check to display tax and shipping info in product boxes (catalog pages). This option is used in Germany. shipping]]> shipping]]> shipping]]> shipping]]> Also note that some attribute control types that support custom user input (e.g. file upload, textboxes, date picker) are useless with attribute combinations Vendor Info Allow customers to select page size Whether customers are allowed to select the page size from a predefined list of options. Meta description Meta description to be added to vendor page header. Meta keywords Meta keywords to be added to vendor page header. Meta title Override the page title. The default is the name of the vendor. Page size Set the page size for products in this vendor e.g. ''4'' products per page. Page Size options (comma separated) Comma separated list of page size options (e.g. 10, 5, 15, 20). First option is the default page size if none are selected. Search engine friendly page name Set a search engine friendly page name e.g. ''the-best-vendor'' to make your page URL ''http://www.yourStore.com/the-best-vendor''. Leave empty to generate it automatically based on the name of the vendor. Display order Set the vendor''s display order. 1 represents the top of the list. Picture 5 Ignore ACL rules (sitewide) Check to ignore ACL rules configured for entities (sitewide). Recommended to enable this setting if you don''t use it. It can significantly improve performance. Ignore "limit per store" rules (sitewide) Check to ignore "limit per store" rules configured for entities (sitewide). Recommended to enable this setting if you have only one store or don''t use it. It can significantly improve performance. Performance. You use only one store. Recommended to ignore store limitations (catalog settings) Performance. Recommended to ignore ACL rules if you don''t use them (catalog settings) The comma separated list of possible HTTP_HOST values (for example, "yourstore.com,www.yourstore.com"). This property is required only when you run a multi-store solution to determine the current store. ' CREATE TABLE #LocaleStringResourceTmp ( [ResourceName] [nvarchar](200) NOT NULL, [ResourceValue] [nvarchar](max) NOT NULL ) INSERT INTO #LocaleStringResourceTmp (ResourceName, ResourceValue) SELECT nref.value('@Name', 'nvarchar(200)'), nref.value('Value[1]', 'nvarchar(MAX)') FROM @resources.nodes('//Language/LocaleResource') AS R(nref) --do it for each existing language DECLARE @ExistingLanguageID int DECLARE cur_existinglanguage CURSOR FOR SELECT [ID] FROM [Language] OPEN cur_existinglanguage FETCH NEXT FROM cur_existinglanguage INTO @ExistingLanguageID WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @ResourceName nvarchar(200) DECLARE @ResourceValue nvarchar(MAX) DECLARE cur_localeresource CURSOR FOR SELECT ResourceName, ResourceValue FROM #LocaleStringResourceTmp OPEN cur_localeresource FETCH NEXT FROM cur_localeresource INTO @ResourceName, @ResourceValue WHILE @@FETCH_STATUS = 0 BEGIN IF (EXISTS (SELECT 1 FROM [LocaleStringResource] WHERE LanguageID=@ExistingLanguageID AND ResourceName=@ResourceName)) BEGIN UPDATE [LocaleStringResource] SET [ResourceValue]=@ResourceValue WHERE LanguageID=@ExistingLanguageID AND ResourceName=@ResourceName END ELSE BEGIN INSERT INTO [LocaleStringResource] ( [LanguageId], [ResourceName], [ResourceValue] ) VALUES ( @ExistingLanguageID, @ResourceName, @ResourceValue ) END IF (@ResourceValue is null or @ResourceValue = '') BEGIN DELETE [LocaleStringResource] WHERE LanguageID=@ExistingLanguageID AND ResourceName=@ResourceName END FETCH NEXT FROM cur_localeresource INTO @ResourceName, @ResourceValue END CLOSE cur_localeresource DEALLOCATE cur_localeresource --fetch next language identifier FETCH NEXT FROM cur_existinglanguage INTO @ExistingLanguageID END CLOSE cur_existinglanguage DEALLOCATE cur_existinglanguage DROP TABLE #LocaleStringResourceTmp GO --'Clear log' schedule task (disabled by default) IF NOT EXISTS ( SELECT 1 FROM [dbo].[ScheduleTask] WHERE [Type] = N'Nop.Services.Logging.ClearLogTask, Nop.Services') BEGIN INSERT [dbo].[ScheduleTask] ([Name], [Seconds], [Type], [Enabled], [StopOnError]) VALUES (N'Clear log', 3600, N'Nop.Services.Logging.ClearLogTask, Nop.Services', 0, 0) END GO --delete checkout attributes. now they store specific DELETE FROM [GenericAttribute] WHERE [KeyGroup] = N'Customer' and [Key] = N'CheckoutAttributes' GO --Store mapping for checkout attributes IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[CheckoutAttribute]') and NAME='LimitedToStores') BEGIN ALTER TABLE [CheckoutAttribute] ADD [LimitedToStores] bit NULL END GO UPDATE [CheckoutAttribute] SET [LimitedToStores] = 0 WHERE [LimitedToStores] IS NULL GO ALTER TABLE [CheckoutAttribute] ALTER COLUMN [LimitedToStores] bit NOT NULL GO --topic SEO names IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[temp_topic_generate_sename]') AND OBJECTPROPERTY(object_id,N'IsProcedure') = 1) DROP PROCEDURE [dbo].[temp_topic_generate_sename] GO CREATE PROCEDURE [dbo].[temp_topic_generate_sename] ( @entity_id int, @topic_system_name nvarchar(1000), @result nvarchar(1000) OUTPUT ) AS BEGIN --get current name DECLARE @sql nvarchar(4000) --if system name is empty, we exit IF (@topic_system_name is null or @topic_system_name = N'') RETURN --generate se name DECLARE @new_sename nvarchar(1000) SET @new_sename = '' --ensure only allowed chars DECLARE @allowed_se_chars varchar(4000) --Note for store owners: add more chars below if want them to be supported when migrating your data SET @allowed_se_chars = N'abcdefghijklmnopqrstuvwxyz1234567890 _-' DECLARE @l int SET @l = len(@topic_system_name) DECLARE @p int SET @p = 1 WHILE @p <= @l BEGIN DECLARE @c nvarchar(1) SET @c = substring(@topic_system_name, @p, 1) IF CHARINDEX(@c,@allowed_se_chars) > 0 BEGIN SET @new_sename = @new_sename + @c END SET @p = @p + 1 END --replace spaces with '-' SELECT @new_sename = REPLACE(@new_sename,' ','-'); WHILE CHARINDEX('--',@new_sename) > 0 SELECT @new_sename = REPLACE(@new_sename,'--','-'); WHILE CHARINDEX('__',@new_sename) > 0 SELECT @new_sename = REPLACE(@new_sename,'__','_'); --ensure not empty IF (@new_sename is null or @new_sename = '') SELECT @new_sename = ISNULL(CAST(@entity_id AS nvarchar(max)), '0'); --lowercase SELECT @new_sename = LOWER(@new_sename) --ensure this sename is not reserved WHILE (1=1) BEGIN DECLARE @sename_is_already_reserved bit SET @sename_is_already_reserved = 0 SET @sql = 'IF EXISTS (SELECT 1 FROM [UrlRecord] WHERE [Slug] = @sename) BEGIN SELECT @sename_is_already_reserved = 1 END' EXEC sp_executesql @sql,N'@sename nvarchar(1000), @sename_is_already_reserved nvarchar(4000) OUTPUT',@new_sename,@sename_is_already_reserved OUTPUT IF (@sename_is_already_reserved > 0) BEGIN --add some digit to the end in this case SET @new_sename = @new_sename + '-1' END ELSE BEGIN BREAK END END --return SET @result = @new_sename END GO --update [sename] column for topics BEGIN DECLARE @sename_existing_entity_id int DECLARE cur_sename_existing_entity CURSOR FOR SELECT [Id] FROM [Topic] OPEN cur_sename_existing_entity FETCH NEXT FROM cur_sename_existing_entity INTO @sename_existing_entity_id WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @sename nvarchar(1000) SET @sename = null -- clear cache (variable scope) DECLARE @table_name nvarchar(1000) SET @table_name = N'Topic' DECLARE @topic_system_name nvarchar(1000) SET @topic_system_name = null -- clear cache (variable scope) SELECT @topic_system_name = [SystemName] FROM [Topic] WHERE [Id] = @sename_existing_entity_id --main sename EXEC [dbo].[temp_topic_generate_sename] @entity_id = @sename_existing_entity_id, @topic_system_name = @topic_system_name, @result = @sename OUTPUT IF EXISTS(SELECT 1 FROM [UrlRecord] WHERE [LanguageId]=0 AND [EntityId]=@sename_existing_entity_id AND [EntityName]=@table_name) BEGIN UPDATE [UrlRecord] SET [Slug] = @sename WHERE [LanguageId]=0 AND [EntityId]=@sename_existing_entity_id AND [EntityName]=@table_name END ELSE BEGIN INSERT INTO [UrlRecord] ([EntityId], [EntityName], [Slug], [IsActive], [LanguageId]) VALUES (@sename_existing_entity_id, @table_name, @sename, 1, 0) END --fetch next identifier FETCH NEXT FROM cur_sename_existing_entity INTO @sename_existing_entity_id END CLOSE cur_sename_existing_entity DEALLOCATE cur_sename_existing_entity END GO --drop temporary procedures & functions IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[temp_topic_generate_sename]') AND OBJECTPROPERTY(object_id,N'IsProcedure') = 1) DROP PROCEDURE [temp_topic_generate_sename] GO --New column IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[Product]') and NAME='AllowAddingOnlyExistingAttributeCombinations') BEGIN ALTER TABLE [Product] ADD [AllowAddingOnlyExistingAttributeCombinations] bit NULL END GO UPDATE [Product] SET [AllowAddingOnlyExistingAttributeCombinations] = 0 WHERE [AllowAddingOnlyExistingAttributeCombinations] IS NULL GO ALTER TABLE [Product] ALTER COLUMN [AllowAddingOnlyExistingAttributeCombinations] bit NOT NULL GO --Store mapping for countries IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[Country]') and NAME='LimitedToStores') BEGIN ALTER TABLE [Country] ADD [LimitedToStores] bit NULL END GO UPDATE [Country] SET [LimitedToStores] = 0 WHERE [LimitedToStores] IS NULL GO ALTER TABLE [Country] ALTER COLUMN [LimitedToStores] bit NOT NULL GO --a new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'localizationsettings.automaticallydetectlanguage') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'localizationsettings.automaticallydetectlanguage', N'false', 0) END GO --New "customer role" column IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[CustomerRole]') and NAME='PurchasedWithProductId') BEGIN ALTER TABLE [CustomerRole] ADD [PurchasedWithProductId] int NULL END GO UPDATE [CustomerRole] SET [PurchasedWithProductId] = 0 WHERE [PurchasedWithProductId] IS NULL GO ALTER TABLE [CustomerRole] ALTER COLUMN [PurchasedWithProductId] int NOT NULL GO --New column IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[OrderNote]') and NAME='DownloadId') BEGIN ALTER TABLE [OrderNote] ADD [DownloadId] int NULL END GO UPDATE [OrderNote] SET [DownloadId] = 0 WHERE [DownloadId] IS NULL GO ALTER TABLE [OrderNote] ALTER COLUMN [DownloadId] int NOT NULL GO --New column IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[Product_ProductAttribute_Mapping]') and NAME='ValidationMinLength') BEGIN ALTER TABLE [Product_ProductAttribute_Mapping] ADD [ValidationMinLength] int NULL END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[Product_ProductAttribute_Mapping]') and NAME='ValidationMaxLength') BEGIN ALTER TABLE [Product_ProductAttribute_Mapping] ADD [ValidationMaxLength] int NULL END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[CheckoutAttribute]') and NAME='ValidationMinLength') BEGIN ALTER TABLE [CheckoutAttribute] ADD [ValidationMinLength] int NULL END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[CheckoutAttribute]') and NAME='ValidationMaxLength') BEGIN ALTER TABLE [CheckoutAttribute] ADD [ValidationMaxLength] int NULL END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[Product_ProductAttribute_Mapping]') and NAME='ValidationFileAllowedExtensions') BEGIN ALTER TABLE [Product_ProductAttribute_Mapping] ADD [ValidationFileAllowedExtensions] nvarchar(MAX) NULL END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[Product_ProductAttribute_Mapping]') and NAME='ValidationFileMaximumSize') BEGIN ALTER TABLE [Product_ProductAttribute_Mapping] ADD [ValidationFileMaximumSize] int NULL END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[CheckoutAttribute]') and NAME='ValidationFileAllowedExtensions') BEGIN ALTER TABLE [CheckoutAttribute] ADD [ValidationFileAllowedExtensions] nvarchar(MAX) NULL END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[CheckoutAttribute]') and NAME='ValidationFileMaximumSize') BEGIN ALTER TABLE [CheckoutAttribute] ADD [ValidationFileMaximumSize] int NULL END GO DELETE FROM [Setting] WHERE [name] = N'catalogsettings.fileuploadallowedextensions' GO DELETE FROM [Setting] WHERE [name] = N'catalogsettings.fileuploadmaximumsizebytes' GO --a new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'ordersettings.disablebillingaddresscheckoutstep') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'ordersettings.disablebillingaddresscheckoutstep', N'false', 0) END GO --new UPS settings IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'upssettings.passdimensions') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'upssettings.passdimensions', N'true', 0) END GO IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'upssettings.packingtype') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'upssettings.packingtype', N'0', 0) END GO IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'upssettings.packingpackagevolume') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'upssettings.packingpackagevolume', N'5184', 0) END GO IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'upssettings.tracing') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'upssettings.tracing', N'false', 0) END GO --customer attributes IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[CustomerAttribute]') and OBJECTPROPERTY(object_id, N'IsUserTable') = 1) BEGIN CREATE TABLE [dbo].[CustomerAttribute]( [Id] [int] IDENTITY(1,1) NOT NULL, [Name] nvarchar(400) NOT NULL, [IsRequired] [bit] NOT NULL, [AttributeControlTypeId] [int] NOT NULL, [DisplayOrder] [int] NOT NULL, PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ) END GO IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[CustomerAttributeValue]') and OBJECTPROPERTY(object_id, N'IsUserTable') = 1) BEGIN CREATE TABLE [dbo].[CustomerAttributeValue]( [Id] [int] IDENTITY(1,1) NOT NULL, [CustomerAttributeId] [int] NOT NULL, [Name] nvarchar(400) NOT NULL, [IsPreSelected] [bit] NOT NULL, [DisplayOrder] [int] NOT NULL, PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ) END GO IF EXISTS (SELECT 1 FROM sys.objects WHERE name = 'CustomerAttributeValue_CustomerAttribute' AND parent_object_id = Object_id('CustomerAttributeValue') AND Objectproperty(object_id,N'IsForeignKey') = 1) ALTER TABLE dbo.CustomerAttributeValue DROP CONSTRAINT CustomerAttributeValue_CustomerAttribute GO ALTER TABLE [dbo].[CustomerAttributeValue] WITH CHECK ADD CONSTRAINT [CustomerAttributeValue_CustomerAttribute] FOREIGN KEY([CustomerAttributeId]) REFERENCES [dbo].[CustomerAttribute] ([Id]) ON DELETE CASCADE GO --a new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'storeinformationsettings.responsivedesignsupported') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'storeinformationsettings.responsivedesignsupported', N'true', 0) END GO --remove some overridden settings that should not exist for stores DELETE FROM [Setting] WHERE [name] = N'ordersettings.returnrequestactions' AND [StoreId] > 0 GO DELETE FROM [Setting] WHERE [name] = N'ordersettings.returnrequestreasons' AND [StoreId] > 0 GO --a new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'taxsettings.forcetaxexclusionfromordersubtotal') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'taxsettings.forcetaxexclusionfromordersubtotal', N'false', 0) END GO --a new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'catalogsettings.displaytaxshippinginfofooter') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'catalogsettings.displaytaxshippinginfofooter', N'false', 0) END GO --a new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'catalogsettings.displaytaxshippinginfoproductdetailspage') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'catalogsettings.displaytaxshippinginfoproductdetailspage', N'false', 0) END GO --a new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'catalogsettings.displaytaxshippinginfoproductboxes') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'catalogsettings.displaytaxshippinginfoproductboxes', N'false', 0) END GO --a new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'catalogsettings.enabledynamicskumpngtinupdate') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'catalogsettings.enabledynamicskumpngtinupdate', N'false', 0) END GO --New columns for vendor IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[Vendor]') and NAME='MetaKeywords') BEGIN ALTER TABLE [Vendor] ADD [MetaKeywords] nvarchar(400) NULL END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[Vendor]') and NAME='MetaDescription') BEGIN ALTER TABLE [Vendor] ADD [MetaDescription] nvarchar(MAX) NULL END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[Vendor]') and NAME='MetaTitle') BEGIN ALTER TABLE [Vendor] ADD [MetaTitle] nvarchar(400) NULL END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[Vendor]') and NAME='PageSize') BEGIN ALTER TABLE [Vendor] ADD [PageSize] int NULL END GO UPDATE [Vendor] SET [PageSize] = 4 WHERE [PageSize] IS NULL GO ALTER TABLE [Vendor] ALTER COLUMN [PageSize] int NOT NULL GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[Vendor]') and NAME='AllowCustomersToSelectPageSize') BEGIN ALTER TABLE [Vendor] ADD [AllowCustomersToSelectPageSize] bit NULL END GO UPDATE [Vendor] SET [AllowCustomersToSelectPageSize] = 1 WHERE [AllowCustomersToSelectPageSize] IS NULL GO ALTER TABLE [Vendor] ALTER COLUMN [AllowCustomersToSelectPageSize] bit NOT NULL GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[Vendor]') and NAME='PageSizeOptions') BEGIN ALTER TABLE [Vendor] ADD [PageSizeOptions] nvarchar(200) NULL END GO UPDATE [Vendor] SET [PageSizeOptions] = N'8, 4, 12' WHERE [PageSizeOptions] IS NULL GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[Vendor]') and NAME='DisplayOrder') BEGIN ALTER TABLE [Vendor] ADD [DisplayOrder] int NULL END GO UPDATE [Vendor] SET [DisplayOrder] = 1 WHERE [DisplayOrder] IS NULL GO ALTER TABLE [Vendor] ALTER COLUMN [DisplayOrder] int NOT NULL GO IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'vendorsettings.defaultvendorpagesizeoptions') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'vendorsettings.defaultvendorpagesizeoptions', N'8, 4, 12', 0) END GO DELETE FROM [Setting] WHERE [name] = N'vendorsettings.pagesize' GO DELETE FROM [Setting] WHERE [name] = N'vendorsettings.allowcustomerstoselectpagesize' GO DELETE FROM [Setting] WHERE [name] = N'vendorsettings.pagesizeoptions' GO --vendor SEO names IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[temp_vendor_generate_sename]') AND OBJECTPROPERTY(object_id,N'IsProcedure') = 1) DROP PROCEDURE [dbo].[temp_vendor_generate_sename] GO CREATE PROCEDURE [dbo].[temp_vendor_generate_sename] ( @entity_id int, @vendor_name nvarchar(1000), @result nvarchar(1000) OUTPUT ) AS BEGIN --get current name DECLARE @sql nvarchar(4000) --if name is empty, we exit IF (@vendor_name is null or @vendor_name = N'') RETURN --generate se name DECLARE @new_sename nvarchar(1000) SET @new_sename = '' --ensure only allowed chars DECLARE @allowed_se_chars varchar(4000) --Note for store owners: add more chars below if want them to be supported when migrating your data SET @allowed_se_chars = N'abcdefghijklmnopqrstuvwxyz1234567890 _-' DECLARE @l int SET @l = len(@vendor_name) DECLARE @p int SET @p = 1 WHILE @p <= @l BEGIN DECLARE @c nvarchar(1) SET @c = substring(@vendor_name, @p, 1) IF CHARINDEX(@c,@allowed_se_chars) > 0 BEGIN SET @new_sename = @new_sename + @c END SET @p = @p + 1 END --replace spaces with '-' SELECT @new_sename = REPLACE(@new_sename,' ','-'); WHILE CHARINDEX('--',@new_sename) > 0 SELECT @new_sename = REPLACE(@new_sename,'--','-'); WHILE CHARINDEX('__',@new_sename) > 0 SELECT @new_sename = REPLACE(@new_sename,'__','_'); --ensure not empty IF (@new_sename is null or @new_sename = '') SELECT @new_sename = ISNULL(CAST(@entity_id AS nvarchar(max)), '0'); --lowercase SELECT @new_sename = LOWER(@new_sename) --ensure this sename is not reserved WHILE (1=1) BEGIN DECLARE @sename_is_already_reserved bit SET @sename_is_already_reserved = 0 SET @sql = 'IF EXISTS (SELECT 1 FROM [UrlRecord] WHERE [Slug] = @sename) BEGIN SELECT @sename_is_already_reserved = 1 END' EXEC sp_executesql @sql,N'@sename nvarchar(1000), @sename_is_already_reserved nvarchar(4000) OUTPUT',@new_sename,@sename_is_already_reserved OUTPUT IF (@sename_is_already_reserved > 0) BEGIN --add some digit to the end in this case SET @new_sename = @new_sename + '-1' END ELSE BEGIN BREAK END END --return SET @result = @new_sename END GO --update [sename] column for vendors BEGIN DECLARE @sename_existing_entity_id int DECLARE cur_sename_existing_entity CURSOR FOR SELECT [Id] FROM [Vendor] OPEN cur_sename_existing_entity FETCH NEXT FROM cur_sename_existing_entity INTO @sename_existing_entity_id WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @sename nvarchar(1000) SET @sename = null -- clear cache (variable scope) DECLARE @table_name nvarchar(1000) SET @table_name = N'Vendor' DECLARE @vendor_name nvarchar(1000) SET @vendor_name = null -- clear cache (variable scope) SELECT @vendor_name = [Name] FROM [Vendor] WHERE [Id] = @sename_existing_entity_id --main sename EXEC [dbo].[temp_vendor_generate_sename] @entity_id = @sename_existing_entity_id, @vendor_name = @vendor_name, @result = @sename OUTPUT IF EXISTS(SELECT 1 FROM [UrlRecord] WHERE [LanguageId]=0 AND [EntityId]=@sename_existing_entity_id AND [EntityName]=@table_name) BEGIN UPDATE [UrlRecord] SET [Slug] = @sename WHERE [LanguageId]=0 AND [EntityId]=@sename_existing_entity_id AND [EntityName]=@table_name END ELSE BEGIN INSERT INTO [UrlRecord] ([EntityId], [EntityName], [Slug], [IsActive], [LanguageId]) VALUES (@sename_existing_entity_id, @table_name, @sename, 1, 0) END --fetch next identifier FETCH NEXT FROM cur_sename_existing_entity INTO @sename_existing_entity_id END CLOSE cur_sename_existing_entity DEALLOCATE cur_sename_existing_entity END GO --drop temporary procedures & functions IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[temp_vendor_generate_sename]') AND OBJECTPROPERTY(object_id,N'IsProcedure') = 1) DROP PROCEDURE [temp_vendor_generate_sename] GO --new setings IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'catalogsettings.ignoreacl') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'catalogsettings.ignoreacl', N'false', 0) END GO IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'catalogsettings.ignorestorelimitations') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'catalogsettings.ignorestorelimitations', N'false', 0) END GO IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'shippingsettings.usecuberootmethod') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'shippingsettings.usecuberootmethod', N'true', 0) END GO