--upgrade scripts from nopCommerce 4.00 to 4.10 --new locale resources declare @resources xml --a resource will be deleted if its value is empty set @resources=' The currency code. For a list of currency codes, go to: https://en.wikipedia.org/wiki/ISO_4217 Avatar Export/Import products. Allow download images Check if images can be downloaded from remote server when exporting products Search keywords Search topic(s) by specific keywords. Check to display reviews written in the current store only (on a product details page and on the account product reviews page). Grid List Default view mode Choose the default view mode for catalog pages. End date The end date for the search. Start date The start date for the search. A customer should be approved by administrator Registration is disabled Email validation is required after registration Standard account creation The entered SKU is already reserved for the product ''{0}'' The entered SKU is already reserved for one of combinations of the product ''{0}'' Please select required attribute(s) Vendor name Vendor name Vendor name Show vendor name on order details page Check to show vendor name of product on the order details page. Edit combination Picture Choose a picture associated to this attribute combination. This picture will replace the main product image when this product attribute combination is selected. No picture URL with coupon code The sample link that includes a discount coupon code, so that customers do not have to input the coupon code at checkout. You can also use this query parameter with any other link to your store, for example link to certain product or category. Notify customer about product review reply Check to notify customer about product review reply. Notify customer about product review reply in Configuration - Settings - Catalog settings.]]> This message template is used to notify an affiliate that the certain order was paid. The order gets the status Paid when the amount was charged. This message template is used to notify an affiliate that the certain order was placed. Save changes Cancel changes Used by products Product Published The URL of your store e.g. http://www.yourstore.com/ or https://www.yourstore.com/mystore/. Sort by ascending Check if the product reviews should be sorted by creation date as ascending Export/Import products. Allow splitting file Check if you want to import products from individual files of the optimal size, which were automatically created from the main file. This function will help you import a large amount of data with a smaller delay. Shopping carts and wishlists Shopping cart type Choose a shopping cart type. Current shopping cart and wishlist Price adjustment. Use percentage Determines whether to apply a percentage to the product. If not enabled, a fixed value is used. Price adjustment. Use percentage Determines whether to apply a percentage to the product. If not enabled, a fixed value is used. The price adjustment applied when choosing this attribute value. For example ''10'' to add 10 dollars. Or 10% if ''Use percentage'' is ticked. The price adjustment applied when choosing this attribute value. For example ''10'' to add 10 dollars. Or 10% if ''Use percentage'' is ticked. Vendor attributes The new attribute has been added successfully. Add new vendor attribute back to vendor settings The attribute has been deleted successfully. You can manage additional vendor attributes below. Edit vendor attribute details Control type Choose how to display your attribute values. Display order The vendor attribute display order. 1 represents the first item in the list. Required When an attribute is required, vendors must choose an appropriate attribute value before they can continue. Name The name of the vendor attribute. Please provide a name. Attribute info The attribute has been updated successfully. Attribute values Add a new attribute value Edit attribute value details Display order The display order of the attribute value. 1 represents the first item in attribute value list. Pre-selected Determines whether this attribute value is pre-selected. Name The name of the vendor attribute value. Please provide a name. You need to save the vendor attribute before you can add values for it. Added a new vendor attribute (ID = {0}) Added a new vendor attribute value (ID = {0}) Deleted a vendor attribute (ID = {0}) Deleted a vendor attribute value (ID = {0}) Edited a vendor attribute (ID = {0}) Edited a vendor attribute value (ID = {0}) County County is required. County County is required. County Enter county. County is required. ''County'' enabled Set if ''County'' is enabled. ''County'' required Check if ''County'' is required. ''County'' enabled Set if ''County'' is enabled. ''County'' required Check if ''County'' is required. County The county. County is required. County County Search by a specific county. Categories with the same name are not supported in the same category level. Check your category list in "Catalog -> Categories" page Display "Apply for vendor account" Check if "Apply for vendor account" menu item should be displayed in the footer. Vendor functionality should be also enabled in this case. Display "Blog" Check if "Blog" menu item should be displayed in the footer. Blog functionality should be also enabled in this case. Display "Compare products list" Check if "Compare products list" menu item should be displayed in the footer. Compare products functionality should be also enabled in this case. Display "Contact us" Check if "Contact us" menu item should be displayed in the footer. Display "Addresses" Check if "Addresses" menu item should be displayed in the footer. Display "My account" Check if "My account" menu item should be displayed in the footer. Display "Orders" Check if "Orders" menu item should be displayed in the footer. Display "Forums" Check if "Forums" menu item should be displayed in the footer. Display "New products" Check if "New products" menu item should be displayed in the footer. Display "News" Check if "News" menu item should be displayed in the footer. Display "Search" Check if "Search" menu item should be displayed in the footer. Display "Recently viewed products" Check if "Recently viewed products" menu item should be displayed in the footer. Display "Shopping cart" Check if "Shopping cart" menu item should be displayed in the footer. Display "Sitemap" Check if "Sitemap" menu item should be displayed in the footer. Display "Wishlist" Check if "Wishlist" menu item should be displayed in the footer. Footer items Billing phone number Filter by customer billing phone number. Sitemap page size A number of items displayed on one sitemap page. Generate several combinations Choose some attribute values to generate necessary combinations Generate * - required attribute There are required attributes: {0} Activate points immediately Activate bonus points immediately after they are added. Reward points activation Specify how many days (hours) must elapse before earned points become active. Enter message (comment). Enter points to add. Negative values are also supported (reduce points). Choose a store. It''s useful only when you have "Points accumulated for all stores" setting disabled. By default not all site pages are SSL protected. Check to force SSL for the entire site. This setting is highly recommended when you have SSL enabled on your store. Date End date Date End date Unused reward points from {0} have expired Registration points validity Specify number of days when the points awarded for registration will be valid. Days Purchases points validity Specify number of days when the points awarded for purchases will be valid. Days Points validity Specify number of days when the awarded points will be valid (only for positive amount of points). Days Minimum order total Specify the minimum order total (exclude shipping cost) to award points for purchases. Checkout disabled Check to disable the checkout process (a read-only mode where ordering is turned off temporarily). Sorry, checkout process is temporary disabled Limited to stores Option to limit this poll to a certain store. If you have multiple stores, choose one or several from the list. If you don''t use this option just leave this field empty. Store Search by a specific store. Remove required products Remove required products from the cart if the main one is removed. This product requires the following product is added to the cart in the quantity of {1}: {0} This product is required in the quantity of {0} Order weight from Order weight from. Order weight to Order weight to. Order subtotal from Order subtotal from. Order subtotal to Order subtotal to. Order subtotal from Order subtotal from. Order subtotal to Order subtotal to. By Weight/Total Fixed Rate Rate Store If an asterisk is selected, then this shipping rate will apply to all stores. Warehouse If an asterisk is selected, then this shipping rate will apply to all warehouses. Country If an asterisk is selected, then this shipping rate will apply to all customers, regardless of the country. State / province If an asterisk is selected, then this shipping rate will apply to all customers from the given country, regardless of the state. Zip Zip / postal code. If zip is empty, then this shipping rate will apply to all customers from the given country or state, regardless of the zip code. Shipping method Choose shipping method. Order weight from Order weight from. Order weight to Order weight to. Additional fixed cost Specify an additional fixed cost per shopping cart for this option. Set to 0 if you don''t want an additional fixed cost to be applied. Lower weight limit Lower weight limit. This field can be used for \"per extra weight unit\" scenarios. Charge percentage (of subtotal) Charge percentage (of subtotal). Rate per weight unit Rate per weight unit. Limit shipping methods to configured ones If you check this option, then your customers will be limited to shipping options configured here. Otherwise, they''ll be able to choose any existing shipping options even they are not configured here (zero shipping fee in this case). Order weight from Order weight from. Order weight to Order weight to. Data Add record Formula to calculate rates [additional fixed cost] + ([order total weight] - [lower weight limit]) * [rate per weight unit] + [order subtotal] * [charge percentage] Adding a new row with zero value isn''t allowed Username is not valid Username validation is enabled Check to enable username validation (when registering or changing on the "My Account" page) Use regex for username validation Check to use a regular expression for username validation (when registering or changing on the "My Account" page) Username validation rule Set the validation rule for username. You can specify a list of allowed characters or a regular expression. If you use a regular expression check the "Use regex for username validation" setting. The regular expression for username validation is incorrect Delete gift card usage history after order cancellation Check to delete gift card usage history after order cancellation (multi-warehouse) --- Choose the limitation of discount. This parameter will not be taken into account for recurring products/orders. The reCAPTCHA response is invalid or malformed. Please try again. Payment methods Payment methods and restrictions Maximum reward points to use per order Customers won''t be able to use more than X reward points per one order. Set to 0 if you do not want to use this setting. Use my reward points, {0} reward points ({1}) available for this order Export/Import related entities using name Check if related entities should be exported/imported using name. Manufacturers with the following names and/or IDs don''t exist: {0} Categories with the following names and/or IDs don''t exist: {0} Paste the tracking code generated by Google Analytics here. {GOOGLEID} and {CUSTOMER_TRACKING} will be dynamically replaced. GDPR settings Common Consents The new consent has been added successfully. Add consent back to consent list The consent has been deleted successfully. Display during registration Check to display this consent on the registration page. Display on ''customer info'' page Check to display this consent on the ''customer info'' page. Display order The consent display order. 1 represents the first item in the list. Edit consent Is required Check if this consent is required to be ticked. Message Enter message (question) displayed to customers. Please provide a message. Required message Enter message (error) displayed when this consent is not ticked by a customer. Please provide a required message. The GDPR consent has been updated successfully. GDPR enabled Check to enable GDPR (General Data Protection Regulation). Log "newsletter" consent Check to log "newsletter" consent (if this feature is enabled in your store). Log "accept privacy policy" consent Check to log "accept privacy policy" consent (if this feature is enabled in your store). Consent (agree) Consent (disagree) Export data Delete customer GDPR requests (log) Date Customer info Request details Request type Email Search by a specific email (exact match). Request type Search by request type. Requested to delete account We''ll process your request as soon as possible Exported personal data Newsletter Privacy policy GDPR tools Right to be Forgotten Delete account You can use the button below to remove your personal and other data from our store. Keep in mind that this process will delete your account, so you will no longer be able to access or use it anymore. Export personal Data Export You can use the button below to download all the data we store and use for a better experience in our store. GDPR Permanent delete Export data Image squares Are you sure you want to delete selected items? View all results... Show a link to all search results in the autocomplete box Determines whether the link to all results should be displayed in the autocomplete search box. Displayed if the number of items found is greater than the displayed quantity in the autocomplete box. Re-index database tables Re-indexing of database tables complete Modifies existing tables by rebuilding the index. When you execute re-indexing in a table, only the statistics associated with the indexes are updated. Automatic or manual statistics created in the table (instead of an index) are not updated. Re-indexing... Re-index pre-order availability Display the date for a pre-order availability Check to display the date for pre-order availability. Allow customers to check gift card balance Check to allow customers to check gift card balance. If checked, then CAPTCHA setting must be enabled in the admin area. This feature is potentially not safe and CAPTCHA is needed to prevent and complicate bruteforce. Check gift card balance Check gift card balance Check gift card Coupon code is not valid. Coupon code is empty. Database backup functionality works only when your nopCommerce application is deployed on the same server as the database. Otherwise you will have to take care of the backup yourself (contact your system administrator). Select product sort order Select number of products per page Currency selector Search store Enter discount coupon code Enter gift card code You could uninstall and remove the plugin(s) which you don''t use, this might increase performance The store has some error(s) or warning(s). Please find more information on the Warnings page. Review types The new review type has been added successfully. Add a new review type back to catalog settings The review type has been deleted successfully. You can configure a list of review types if you think that a basic review is not enough. Edit review type details Description The description of the review type. Please provide a description. Display order The review type display order. 1 represents the first item on the list. Required When required, customers have to choose an appropriate rating value before they can continue. Name The name of the review type. Please provide a name. Visible to all customers Sets visibility of the review type for all customers. The review type has been updated successfully. Added a new review type (ID = {0}) Edited a review type (ID = {0}) Deleted a review type (ID = {0}) Description Name Rating Visible to all customers back to external authentication method list Show customers join date A value indicating whether to show customers join date. CAPTCHA is a program that can tell whether its user is a human or a computer. You''ve probably seen them — colorful images with distorted text at the bottom of Web registration forms. CAPTCHAs are used by many websites to prevent abuse from "bots" or automated programs usually written to generate spam. No computer programcan read distorted text as well as humans can, so bots cannot navigate sites protected by CAPTCHAs. nopCommerce uses reCAPTCHA.

]]> This message template is used to notify a customer that the certain order was cancelled. The order can ba cancelled by a customer on the account page or by store owner in Customers - Customers in Orders tab or in Sales - Orders. This message template is used to notify a customer that the certain recurring payment is cancelled. Payment can be cancelled by a customer in the account page or by a store owner in Sales - Recurring payments in History tab by clicking "Cancel recurring payment" button. This message template is used to notify a store owner that the certain recurring payment is cancelled. Payment can be cancelled by a customer in the account page or by a store owner in Sales - Recurring payments in History tab by clicking "Cancel recurring payment" button. Search for the name of a resource. These are system-internal names for a language entry. All resource names with ''admin.'' in their names, for example, are only displayed in the admin center, all others in the public area of the shop. Resource names with ''hint.'' in the name are helps like this. Select to enable the category path (breadcrumb). This is the bar at the top of the screen that indicates which categories and subcategories the product was viewed in on the product pages. Each sub-element of the bar is a separate hyperlink. Check if your store will be SSL secured. SSL (Secure Socket Layer) is the standard security technology for establishing an encrypted connection between a web server and the browser. This ensures that all data exchanged between web server and browser arrives unchanged. Bulk edit Reports Low stock Published Search by a "Published" property. All Published only Unpublished only Bestsellers Billing country Filter by order billing country. Bestsellers by amount Bestsellers by quantity Category Search in a specific category. End date The end date for the search. Name Total amount (excl tax) Total quantity Manufacturer Search in a specific manufacturer. Order status Search by a specific order status e.g. Complete. Payment status Search by a specific payment status e.g. Paid. Run report Start date The start date for the search. Store Filter report by orders placed in a specific store. Vendor Search by a specific vendor. Products never purchased End date The end date for the search. Name Run report Category Load products only from a specific category. Manufacturer Load products only from a specific manufacturer. Store Load products only from a specific store (available in this store). Vendor Load products only by a specific vendor (owned by this vendor). Start date The start date for the search. Country sales End date The end date for the search. Country Order total Number of orders Order status Search by a specific order status e.g. Complete. Payment status Search by a specific payment status e.g. Paid. Run report Start date The start date for the search. Customer reports Customers by number of orders Customers by order total End date The end date for the search. Customer Number of orders Order total Order status Search by a specific order status e.g. Complete. Payment status Search by a specific payment status e.g. Paid. Shipping status Search by a specific shipping status e.g. Not yet shipped. Start date The start date for the search. Registered customers Count Period In the last 14 days In the last 7 days In the last month In the last year Run report New customers Month Week Year Blog post ID Search by blog post ID. News item ID Search by news item ID. Activity log Activity log Activity log type The activity log type. Activity log type This message template is used to notify a customer that the certain order was cancelled. The order can be cancelled by a customer on the account page or by store owner in Customers - Customers in Orders tab or in Sales - Orders. CAPTCHA is a program that can tell whether its user is a human or a computer. You''ve probably seen them — colorful images with distorted text at the bottom of Web registration forms. CAPTCHAs are used by many websites to prevent abuse from "bots" or automated programs usually written to generate spam. No computer program can read distorted text as well as humans can, so bots cannot navigate sites protected by CAPTCHAs. nopCommerce uses reCAPTCHA.

]]> By creating an account on our website, you will be able to shop faster, be up to date on an orders status, and keep track of the orders you have previously made. Check to mark the product as new. Use this option for promoting new products. Check to specify a condition (depending on another attribute) when this attribute should be enabled (visible). Check to specify a condition (depending on another attribute) when this attribute should be enabled (visible). In order to use this functionality, you have to disable the following setting: Configuration > Catalog settings > Ignore discounts (sitewide). Choose the warehouse which will be used when calculating shipping rates. You can manage warehouses by selecting Configuration > Shipping > Warehouses. Date and time should be entered in Coordinated Universal Time (UTC) Date and time should be entered in Coordinated Universal Time (UTC) For conditional expressions use the token %if (your conditions) ... endif% If checked, this discount can be used with other ones simultaneously. Please note that this feature works only for discounts with the same discount type. Right now, discounts with distinct types are already cumulative. In order to use this functionality, you have to disable the following setting: Catalog settings > Ignore "limit per store" rules. In order to use this functionality, you have to disable the following setting: Catalog settings > Ignore ACL rules. Choose one or several customer roles i.e. administrators, vendors, guests, who will be able to see this product in catalog. If you don''t need this option just leave this field empty. In order to use this functionality, you have to disable the following setting: Configuration > Catalog settings > Ignore ACL rules (sitewide). Option to limit this product to a certain store. If you have multiple stores, choose one or several from the list. If you don''t use this option just leave this field empty. In order to use this functionality, you have to disable the following setting: Configuration > Catalog settings > Ignore "limit per store" rules (sitewide). It seems that you use Redis server for caching, keep in mind that enabling this setting creates a lot of traffic between the Redis server and the application because of the large number of locales. Search by a specific order status e.g. Complete. Search by a specific payment status e.g. Paid. Search by a specific shipping status e.g. Not yet shipped. Select inventory method. There are three methods: Don’t track inventory, Track inventory and Track inventory by attributes. You should use Track inventory by attributes when the product has different combinations of these attributes and then manage inventory for these combinations. Select to enable the category path (breadcrumb). This is the bar at the top of the screen that indicates which categories and subcategories the product was viewed in on the product pages. Each sub-element of the bar is a separate hyperlink. SKU Specify opening hours of the pickup point (Monday - Friday: 09:00 - 19:00 for example). Tax displaying Minimum stock qty field.]]> When enabled, IP addresses of customers will be stored. When disabled, it can improve performance. Furthermore, it''s prohibited to store IP addresses in some countries (private customer data). When enabled, the last visited page will be stored. When disabled, it can improve performance. To configure authentication with Facebook, please follow these steps:

  1. Navigate to the Facebook for Developers page and sign in. If you don''t already have a Facebook account, use the Sign up for Facebook link on the login page to create one.
  2. Tap the + Add a New App button in the upper right corner to create a new App ID. (If this is your first app with Facebook, the text of the button will be Create a New App.)
  3. Fill out the form and tap the Create App ID button.
  4. The Product Setup page is displayed, letting you select the features for your new app. Select the option Facebook Login and press Set up.
  5. Click on Settings on the left menu and in section Client OAuth Settings select the field Valid OAuth Redirect URIs
  6. Enter \"YourStoreUrl/signin-facebook\" in that field.
  7. Click Save Changes.
  8. Click the Dashboard link in the left navigation.
  9. Copy your App ID and App secret below.


]]> WARNING. It is not recommended to do this on live sites because:

  1. Product prices, order totals, shipping rates, etc are not automatically converted to a new currency
  2. Currency exhange rates are not automatically updated
]]> WARNING. It is not recommended to do this on live sites because:

  1. Other rates have to be updated manually
  2. You have to ensure that each product has a valid measure (dimension and weights) - they are not adjusted automatically
]]> Billing & shipping Show customer''s location A value indicating whether customer''s location is shown. User agreement Shopping Cart Wishlist Max length of search name is {0} chars Order note can not be empty. ' 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 --new index IF NOT EXISTS (SELECT 1 from sys.indexes WHERE [NAME]=N'IX_GetLowStockProducts' and object_id=object_id(N'[dbo].[Product]')) BEGIN CREATE NONCLUSTERED INDEX [IX_GetLowStockProducts] ON [Product] (Deleted ASC, VendorId ASC, ProductTypeId ASC, ManageInventoryMethodId ASC, MinStockQuantity ASC, UseMultipleWarehouses ASC) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'catalogsettings.exportimportallowdownloadimages') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'catalogsettings.exportimportallowdownloadimages', N'false', 0) END GO --new column IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = object_id('[ActivityLog]') AND NAME = 'EntityId') BEGIN ALTER TABLE [ActivityLog] ADD [EntityId] INT NULL END GO --new column IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = object_id('[ActivityLog]') AND NAME = 'EntityName') BEGIN ALTER TABLE [ActivityLog] ADD [EntityName] NVARCHAR(400) NULL END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'vendorsettings.showvendoronorderdetailspage') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'vendorsettings.showvendoronorderdetailspage', N'false', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'addresssettings.preselectcountryifonlyone') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'addresssettings.preselectcountryifonlyone', N'false', 0) END GO --new column IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = object_id('[ProductAttributeCombination]') AND NAME = 'PictureId') BEGIN ALTER TABLE [ProductAttributeCombination] ADD [PictureId] INT NULL END GO UPDATE [ProductAttributeCombination] SET [PictureId] = 0 WHERE [PictureId] IS NULL ALTER TABLE [ProductAttributeCombination] ALTER COLUMN [PictureId] INT NOT NULL GO -- new message template IF NOT EXISTS (SELECT 1 FROM [dbo].[MessageTemplate] WHERE [Name] = N'ProductReview.Reply.CustomerNotification') BEGIN DECLARE @NewLine AS CHAR(2) = CHAR(13) + CHAR(10) INSERT [dbo].[MessageTemplate] ([Name], [BccEmailAddresses], [Subject], [Body], [IsActive], [AttachedDownloadId], [EmailAccountId], [LimitedToStores], [DelayPeriodId]) VALUES (N'ProductReview.Reply.CustomerNotification', NULL, N'%Store.Name%. Product review reply.', N'

' + @NewLine + '%Store.Name%' + @NewLine + '
' + @NewLine + '
' + @NewLine + 'Hello %Customer.FullName%,' + @NewLine + '
' + @NewLine + 'You received a reply from the store administration to your review for product "%ProductReview.ProductName%".' + @NewLine + '

' + @NewLine, 0, 0, 0, 0, 0) END GO --new column IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = object_id('[ProductReview]') AND NAME = 'CustomerNotifiedOfReply') BEGIN ALTER TABLE [ProductReview] ADD [CustomerNotifiedOfReply] BIT NULL END GO UPDATE [ProductReview] SET [CustomerNotifiedOfReply] = 0 WHERE [CustomerNotifiedOfReply] IS NULL ALTER TABLE [ProductReview] ALTER COLUMN [CustomerNotifiedOfReply] BIT NOT NULL GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'catalogsettings.notifycustomeraboutproductreviewreply') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'catalogsettings.notifycustomeraboutproductreviewreply', N'false', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'catalogsettings.uselinksinrequiredproductwarnings') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'catalogsettings.uselinksinrequiredproductwarnings', N'true', 0) END GO -- new message template IF NOT EXISTS (SELECT 1 FROM [dbo].[MessageTemplate] WHERE [Name] = N'OrderPlaced.AffiliateNotification') BEGIN DECLARE @NewLine AS CHAR(2) = CHAR(13) + CHAR(10) INSERT [dbo].[MessageTemplate] ([Name], [BccEmailAddresses], [Subject], [Body], [IsActive], [AttachedDownloadId], [EmailAccountId], [LimitedToStores], [DelayPeriodId]) VALUES (N'OrderPlaced.AffiliateNotification', NULL, N'%Store.Name%. Order placed', N'

' + @NewLine + '%Store.Name%' + @NewLine + '
' + @NewLine + '
' + @NewLine + '%Customer.FullName% (%Customer.Email%) has just placed an order.' + @NewLine + '
' + @NewLine + '
' + @NewLine + 'Order Number: %Order.OrderNumber%' + @NewLine + '
' + @NewLine + 'Date Ordered: %Order.CreatedOn%' + @NewLine + '
' + @NewLine + '
' + @NewLine + '%Order.Product(s)%' + @NewLine + '

' + @NewLine, 0, 0, 0, 0, 0) END GO -- new message template IF NOT EXISTS (SELECT 1 FROM [dbo].[MessageTemplate] WHERE [Name] = N'OrderPaid.AffiliateNotification') BEGIN DECLARE @NewLine AS CHAR(2) = CHAR(13) + CHAR(10) INSERT [dbo].[MessageTemplate] ([Name], [BccEmailAddresses], [Subject], [Body], [IsActive], [AttachedDownloadId], [EmailAccountId], [LimitedToStores], [DelayPeriodId]) VALUES (N'OrderPaid.AffiliateNotification', NULL, N'%Store.Name%. Order #%Order.OrderNumber% paid', N'

' + @NewLine + '%Store.Name%' + @NewLine + '
' + @NewLine + '
' + @NewLine + 'Order #%Order.OrderNumber% has been just paid.' + @NewLine + '
' + @NewLine + '
' + @NewLine + 'Order Number: %Order.OrderNumber%' + @NewLine + '
' + @NewLine + 'Date Ordered: %Order.CreatedOn%' + @NewLine + '
' + @NewLine + '
' + @NewLine + '%Order.Product(s)%' + @NewLine + '

' + @NewLine, 0, 0, 0, 0, 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'securitysettings.allownonasciicharactersinheaders') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'securitysettings.allownonasciicharactersinheaders', N'true', 0) END GO --drop column IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = object_id('[Store]') AND NAME='SecureUrl') BEGIN ALTER TABLE [Store] DROP COLUMN [SecureUrl] END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'catalogsettings.exportimportsplitproductsfile') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'catalogsettings.exportimportsplitproductsfile', N'false', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'catalogsettings.productreviewssortbycreateddateascending') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'catalogsettings.productreviewssortbycreateddateascending', N'true', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'catalogsettings.exportimportproductscountinonefile') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'catalogsettings.exportimportproductscountinonefile', N'500', 0) END GO --new column IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[ProductAttributeValue]') and NAME='PriceAdjustmentUsePercentage') BEGIN ALTER TABLE [ProductAttributeValue] ADD [PriceAdjustmentUsePercentage] bit NULL END GO UPDATE [ProductAttributeValue] SET [PriceAdjustmentUsePercentage] = 0 WHERE [PriceAdjustmentUsePercentage] IS NULL GO ALTER TABLE [ProductAttributeValue] ALTER COLUMN [PriceAdjustmentUsePercentage] bit NOT NULL GO --new column IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[PredefinedProductAttributeValue]') and NAME='PriceAdjustmentUsePercentage') BEGIN ALTER TABLE PredefinedProductAttributeValue ADD [PriceAdjustmentUsePercentage] bit NULL END GO UPDATE [PredefinedProductAttributeValue] SET [PriceAdjustmentUsePercentage] = 0 WHERE [PriceAdjustmentUsePercentage] IS NULL GO ALTER TABLE [PredefinedProductAttributeValue] ALTER COLUMN [PriceAdjustmentUsePercentage] bit NOT NULL GO --updated setting UPDATE [Setting] SET [Value] = N'true' WHERE [Name] = N'commonsettings.usestoredprocedureforloadingcategories' GO --vendor attributes IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = object_id(N'[VendorAttribute]') AND objectproperty(object_id, N'IsUserTable') = 1) BEGIN CREATE TABLE [dbo].[VendorAttribute] ( [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'[VendorAttributeValue]') and objectproperty(object_id, N'IsUserTable') = 1) BEGIN CREATE TABLE [dbo].[VendorAttributeValue] ( [Id] INT IDENTITY(1,1) NOT NULL, [VendorAttributeId] 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 = 'FK_VendorAttributeValue_VendorAttribute_VendorAttributeId' AND PARENT_OBJECT_ID = object_id('VendorAttributeValue') AND objectproperty(object_id, N'IsForeignKey') = 1) BEGIN ALTER TABLE dbo.VendorAttributeValue DROP CONSTRAINT [FK_VendorAttributeValue_VendorAttribute_VendorAttributeId] END GO ALTER TABLE [dbo].[VendorAttributeValue] WITH CHECK ADD CONSTRAINT [FK_VendorAttributeValue_VendorAttribute_VendorAttributeId] FOREIGN KEY([VendorAttributeId]) REFERENCES [dbo].[VendorAttribute] ([Id]) ON DELETE CASCADE GO --new activity type IF NOT EXISTS (SELECT 1 FROM [ActivityLogType] WHERE [SystemKeyword] = N'AddNewVendorAttribute') BEGIN INSERT [ActivityLogType] ([SystemKeyword], [Name], [Enabled]) VALUES (N'AddNewVendorAttribute', N'Add a new vendor attribute', N'true') END GO --new activity type IF NOT EXISTS (SELECT 1 FROM [ActivityLogType] WHERE [SystemKeyword] = N'EditVendorAttribute') BEGIN INSERT [ActivityLogType] ([SystemKeyword], [Name], [Enabled]) VALUES (N'EditVendorAttribute', N'Edit a vendor attribute', N'true') END GO --new activity type IF NOT EXISTS (SELECT 1 FROM [ActivityLogType] WHERE [SystemKeyword] = N'DeleteVendorAttribute') BEGIN INSERT [ActivityLogType] ([SystemKeyword], [Name], [Enabled]) VALUES (N'DeleteVendorAttribute', N'Delete a vendor attribute', N'true') END GO --new activity type IF NOT EXISTS (SELECT 1 FROM [ActivityLogType] WHERE [SystemKeyword] = N'AddNewVendorAttributeValue') BEGIN INSERT [ActivityLogType] ([SystemKeyword], [Name], [Enabled]) VALUES (N'AddNewVendorAttributeValue', N'Add a new vendor attribute value', N'true') END GO --new activity type IF NOT EXISTS (SELECT 1 FROM [ActivityLogType] WHERE [SystemKeyword] = N'EditVendorAttributeValue') BEGIN INSERT [ActivityLogType] ([SystemKeyword], [Name], [Enabled]) VALUES (N'EditVendorAttributeValue', N'Edit a vendor attribute value', N'true') END GO --new activity type IF NOT EXISTS (SELECT 1 FROM [ActivityLogType] WHERE [SystemKeyword] = N'DeleteVendorAttributeValue') BEGIN INSERT [ActivityLogType] ([SystemKeyword], [Name], [Enabled]) VALUES (N'DeleteVendorAttributeValue', N'Delete a vendor attribute value', N'true') END GO --new activity type IF NOT EXISTS (SELECT 1 FROM [ActivityLogType] WHERE [SystemKeyword] = N'AddNewReviewType') BEGIN INSERT [ActivityLogType] ([SystemKeyword], [Name], [Enabled]) VALUES (N'AddNewReviewType', N'Add a new review type', N'true') END GO --new activity type IF NOT EXISTS (SELECT 1 FROM [ActivityLogType] WHERE [SystemKeyword] = N'DeleteReviewType') BEGIN INSERT [ActivityLogType] ([SystemKeyword], [Name], [Enabled]) VALUES (N'DeleteReviewType', N'Delete a review type', N'true') END GO --new activity type IF NOT EXISTS (SELECT 1 FROM [ActivityLogType] WHERE [SystemKeyword] = N'EditReviewType') BEGIN INSERT [ActivityLogType] ([SystemKeyword], [Name], [Enabled]) VALUES (N'EditReviewType', N'Edit a review type', N'true') END GO --new column IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = object_id('[Address]') AND NAME = 'County') BEGIN ALTER TABLE [Address] ADD [County] NVARCHAR (MAX) NULL END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'displaydefaultfooteritemsettings.DisplayApplyVendorAccountFooterItem') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'displaydefaultfooteritemsettings.DisplayApplyVendorAccountFooterItem', N'true', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'displaydefaultfooteritemsettings.DisplayBlogFooterItem') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'displaydefaultfooteritemsettings.DisplayBlogFooterItem', N'true', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'displaydefaultfooteritemsettings.DisplayCompareProductsFooterItem') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'displaydefaultfooteritemsettings.DisplayCompareProductsFooterItem', N'true', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'displaydefaultfooteritemsettings.DisplayContactUsFooterItem') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'displaydefaultfooteritemsettings.DisplayContactUsFooterItem', N'true', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'displaydefaultfooteritemsettings.DisplayCustomerAddressesFooterItem') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'displaydefaultfooteritemsettings.DisplayCustomerAddressesFooterItem', N'true', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'displaydefaultfooteritemsettings.DisplayCustomerInfoFooterItem') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'displaydefaultfooteritemsettings.DisplayCustomerInfoFooterItem', N'true', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'displaydefaultfooteritemsettings.DisplayCustomerOrdersFooterItem') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'displaydefaultfooteritemsettings.DisplayCustomerOrdersFooterItem', N'true', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'displaydefaultfooteritemsettings.DisplayForumsFooterItem') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'displaydefaultfooteritemsettings.DisplayForumsFooterItem', N'true', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'displaydefaultfooteritemsettings.DisplayNewProductsFooterItem') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'displaydefaultfooteritemsettings.DisplayNewProductsFooterItem', N'true', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'displaydefaultfooteritemsettings.DisplayNewsFooterItem') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'displaydefaultfooteritemsettings.DisplayNewsFooterItem', N'true', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'addresssettings.countyenabled') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'addresssettings.countyenabled', N'false', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'displaydefaultfooteritemsettings.DisplayProductSearchFooterItem') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'displaydefaultfooteritemsettings.DisplayProductSearchFooterItem', N'true', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'addresssettings.countyrequired') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'addresssettings.countyrequired', N'false', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'displaydefaultfooteritemsettings.DisplayRecentlyViewedProductsFooterItem') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'displaydefaultfooteritemsettings.DisplayRecentlyViewedProductsFooterItem', N'true', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'customersettings.countyenabled') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'customersettings.countyenabled', N'false', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'displaydefaultfooteritemsettings.DisplayShoppingCartFooterItem') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'displaydefaultfooteritemsettings.DisplayShoppingCartFooterItem', N'true', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'customersettings.countyrequired') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'customersettings.countyrequired', N'false', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'displaydefaultfooteritemsettings.DisplaySitemapFooterItem') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'displaydefaultfooteritemsettings.DisplaySitemapFooterItem', N'true', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [Name] = N'shippingsettings.shipseparatelyoneitemeach') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'shippingsettings.shipseparatelyoneitemeach', N'true', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'displaydefaultfooteritemsettings.DisplayWishlistFooterItem') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'displaydefaultfooteritemsettings.DisplayWishlistFooterItem', N'true', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'commonsettings.sitemappagesize') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'commonsettings.sitemappagesize', N'200', 0) END GO --rename setting UPDATE [Setting] SET [Name] = N'adminareasettings.useisodateformatinjsonresult' WHERE [Name] = N'adminareasettings.useisodatetimeconverterinjson' GO --new column IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = object_id('[RewardPointsHistory]') AND NAME = 'EndDateUtc') BEGIN ALTER TABLE [RewardPointsHistory] ADD [EndDateUtc] DATETIME NULL END GO --new column IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = object_id('[RewardPointsHistory]') AND NAME = 'ValidPoints') BEGIN ALTER TABLE [RewardPointsHistory] ADD [ValidPoints] INT NULL END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [Name] = N'rewardpointssettings.registrationpointsvalidity') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'rewardpointssettings.registrationpointsvalidity', N'30', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'customersettings.usernamevalidationenabled') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'customersettings.usernamevalidationenabled', N'false', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [Name] = N'rewardpointssettings.purchasespointsvalidity') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'rewardpointssettings.purchasespointsvalidity', N'45', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'customersettings.usernamevalidationuseregex') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'customersettings.usernamevalidationuseregex', N'false', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [Name] = N'rewardpointssettings.minordertotaltoawardpoints') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'rewardpointssettings.minordertotaltoawardpoints', N'0', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'ordersettings.checkoutdisabled') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'ordersettings.checkoutdisabled', N'false', 0) END GO --rename column IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ShippingByWeight]') and OBJECTPROPERTY(object_id, N'IsUserTable') = 1) AND EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[ShippingByWeight]') and NAME='From') BEGIN EXEC sp_RENAME '[dbo].[ShippingByWeight].[From]', 'WeightFrom', 'COLUMN' END GO --rename column IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ShippingByWeight]') and OBJECTPROPERTY(object_id, N'IsUserTable') = 1) AND EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[ShippingByWeight]') and NAME='To') BEGIN EXEC sp_RENAME '[dbo].[ShippingByWeight].[To]', 'WeightTo', 'COLUMN' END GO --new column IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ShippingByWeight]') and OBJECTPROPERTY(object_id, N'IsUserTable') = 1) and NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = object_id('[ShippingByWeight]') AND NAME = 'OrderSubtotalFrom') BEGIN ALTER TABLE [ShippingByWeight] ADD [OrderSubtotalFrom] DECIMAL NULL END GO IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ShippingByWeight]') and OBJECTPROPERTY(object_id, N'IsUserTable') = 1) and EXISTS (SELECT 1 FROM sys.columns WHERE object_id = object_id('[ShippingByWeight]') AND NAME = 'OrderSubtotalFrom') BEGIN UPDATE [ShippingByWeight] SET [OrderSubtotalFrom] = 0 WHERE [OrderSubtotalFrom] IS NULL END GO IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ShippingByWeight]') and OBJECTPROPERTY(object_id, N'IsUserTable') = 1) and EXISTS (SELECT 1 FROM sys.columns WHERE object_id = object_id('[ShippingByWeight]') AND NAME = 'OrderSubtotalFrom') BEGIN ALTER TABLE [ShippingByWeight] ALTER COLUMN [OrderSubtotalFrom] DECIMAL NOT NULL END GO --new column IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ShippingByWeight]') and OBJECTPROPERTY(object_id, N'IsUserTable') = 1) and NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = object_id('[ShippingByWeight]') AND NAME = 'OrderSubtotalTo') BEGIN ALTER TABLE [ShippingByWeight] ADD [OrderSubtotalTo] DECIMAL NULL END GO IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ShippingByWeight]') and OBJECTPROPERTY(object_id, N'IsUserTable') = 1) and EXISTS (SELECT 1 FROM sys.columns WHERE object_id = object_id('[ShippingByWeight]') AND NAME = 'OrderSubtotalTo') BEGIN UPDATE [ShippingByWeight] SET [OrderSubtotalTo] = 1000000 WHERE [OrderSubtotalTo] IS NULL END GO IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ShippingByWeight]') and OBJECTPROPERTY(object_id, N'IsUserTable') = 1) and EXISTS (SELECT 1 FROM sys.columns WHERE object_id = object_id('[ShippingByWeight]') AND NAME = 'OrderSubtotalTo') BEGIN ALTER TABLE [ShippingByWeight] ALTER COLUMN [OrderSubtotalTo] DECIMAL NOT NULL END GO --rename table IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ShippingByWeight]') and OBJECTPROPERTY(object_id, N'IsUserTable') = 1) BEGIN EXEC sp_RENAME '[dbo].[ShippingByWeight]', 'ShippingByWeightByTotalRecord' END GO --new column IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = object_id('[Poll]') and NAME = 'LimitedToStores') BEGIN ALTER TABLE [Poll] ADD [LimitedToStores] BIT NULL END GO UPDATE [Poll] SET [LimitedToStores] = 0 WHERE [LimitedToStores] IS NULL GO ALTER TABLE [Poll] ALTER COLUMN [LimitedToStores] BIT NOT NULL GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'catalogsettings.removerequiredproducts') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'catalogsettings.removerequiredproducts', N'false', 0) END GO -- update the "ProductLoadAllPaged" stored procedure ALTER PROCEDURE [ProductLoadAllPaged] ( @CategoryIds nvarchar(MAX) = null, --a list of category IDs (comma-separated list). e.g. 1,2,3 @ManufacturerId int = 0, @StoreId int = 0, @VendorId int = 0, @WarehouseId int = 0, @ProductTypeId int = null, --product type identifier, null - load all products @VisibleIndividuallyOnly bit = 0, --0 - load all products , 1 - "visible indivially" only @MarkedAsNewOnly bit = 0, --0 - load all products , 1 - "marked as new" only @ProductTagId int = 0, @FeaturedProducts bit = null, --0 featured only , 1 not featured only, null - load all products @PriceMin decimal(18, 4) = null, @PriceMax decimal(18, 4) = null, @Keywords nvarchar(4000) = null, @SearchDescriptions bit = 0, --a value indicating whether to search by a specified "keyword" in product descriptions @SearchManufacturerPartNumber bit = 0, -- a value indicating whether to search by a specified "keyword" in manufacturer part number @SearchSku bit = 0, --a value indicating whether to search by a specified "keyword" in product SKU @SearchProductTags bit = 0, --a value indicating whether to search by a specified "keyword" in product tags @UseFullTextSearch bit = 0, @FullTextMode int = 0, --0 - using CONTAINS with , 5 - using CONTAINS and OR with , 10 - using CONTAINS and AND with @FilteredSpecs nvarchar(MAX) = null, --filter by specification attribute options (comma-separated list of IDs). e.g. 14,15,16 @LanguageId int = 0, @OrderBy int = 0, --0 - position, 5 - Name: A to Z, 6 - Name: Z to A, 10 - Price: Low to High, 11 - Price: High to Low, 15 - creation date @AllowedCustomerRoleIds nvarchar(MAX) = null, --a list of customer role IDs (comma-separated list) for which a product should be shown (if a subjet to ACL) @PageIndex int = 0, @PageSize int = 2147483644, @ShowHidden bit = 0, @OverridePublished bit = null, --null - process "Published" property according to "showHidden" parameter, true - load only "Published" products, false - load only "Unpublished" products @LoadFilterableSpecificationAttributeOptionIds bit = 0, --a value indicating whether we should load the specification attribute option identifiers applied to loaded products (all pages) @FilterableSpecificationAttributeOptionIds nvarchar(MAX) = null OUTPUT, --the specification attribute option identifiers applied to loaded products (all pages). returned as a comma separated list of identifiers @TotalRecords int = null OUTPUT ) AS BEGIN /* Products that filtered by keywords */ CREATE TABLE #KeywordProducts ( [ProductId] int NOT NULL ) DECLARE @SearchKeywords bit, @OriginalKeywords nvarchar(4000), @sql nvarchar(max), @sql_orderby nvarchar(max) SET NOCOUNT ON --filter by keywords SET @Keywords = isnull(@Keywords, '') SET @Keywords = rtrim(ltrim(@Keywords)) SET @OriginalKeywords = @Keywords IF ISNULL(@Keywords, '') != '' BEGIN SET @SearchKeywords = 1 IF @UseFullTextSearch = 1 BEGIN --remove wrong chars (' ") SET @Keywords = REPLACE(@Keywords, '''', '') SET @Keywords = REPLACE(@Keywords, '"', '') --full-text search IF @FullTextMode = 0 BEGIN --0 - using CONTAINS with SET @Keywords = ' "' + @Keywords + '*" ' END ELSE BEGIN --5 - using CONTAINS and OR with --10 - using CONTAINS and AND with --clean multiple spaces WHILE CHARINDEX(' ', @Keywords) > 0 SET @Keywords = REPLACE(@Keywords, ' ', ' ') DECLARE @concat_term nvarchar(100) IF @FullTextMode = 5 --5 - using CONTAINS and OR with BEGIN SET @concat_term = 'OR' END IF @FullTextMode = 10 --10 - using CONTAINS and AND with BEGIN SET @concat_term = 'AND' END --now let's build search string declare @fulltext_keywords nvarchar(4000) set @fulltext_keywords = N'' declare @index int set @index = CHARINDEX(' ', @Keywords, 0) -- if index = 0, then only one field was passed IF(@index = 0) set @fulltext_keywords = ' "' + @Keywords + '*" ' ELSE BEGIN DECLARE @first BIT SET @first = 1 WHILE @index > 0 BEGIN IF (@first = 0) SET @fulltext_keywords = @fulltext_keywords + ' ' + @concat_term + ' ' ELSE SET @first = 0 SET @fulltext_keywords = @fulltext_keywords + '"' + SUBSTRING(@Keywords, 1, @index - 1) + '*"' SET @Keywords = SUBSTRING(@Keywords, @index + 1, LEN(@Keywords) - @index) SET @index = CHARINDEX(' ', @Keywords, 0) end -- add the last field IF LEN(@fulltext_keywords) > 0 SET @fulltext_keywords = @fulltext_keywords + ' ' + @concat_term + ' ' + '"' + SUBSTRING(@Keywords, 1, LEN(@Keywords)) + '*"' END SET @Keywords = @fulltext_keywords END END ELSE BEGIN --usual search by PATINDEX SET @Keywords = '%' + @Keywords + '%' END --PRINT @Keywords --product name SET @sql = ' INSERT INTO #KeywordProducts ([ProductId]) SELECT p.Id FROM Product p with (NOLOCK) WHERE ' IF @UseFullTextSearch = 1 SET @sql = @sql + 'CONTAINS(p.[Name], @Keywords) ' ELSE SET @sql = @sql + 'PATINDEX(@Keywords, p.[Name]) > 0 ' --localized product name SET @sql = @sql + ' UNION SELECT lp.EntityId FROM LocalizedProperty lp with (NOLOCK) WHERE lp.LocaleKeyGroup = N''Product'' AND lp.LanguageId = ' + ISNULL(CAST(@LanguageId AS nvarchar(max)), '0') + ' AND lp.LocaleKey = N''Name''' IF @UseFullTextSearch = 1 SET @sql = @sql + ' AND CONTAINS(lp.[LocaleValue], @Keywords) ' ELSE SET @sql = @sql + ' AND PATINDEX(@Keywords, lp.[LocaleValue]) > 0 ' IF @SearchDescriptions = 1 BEGIN --product short description SET @sql = @sql + ' UNION SELECT p.Id FROM Product p with (NOLOCK) WHERE ' IF @UseFullTextSearch = 1 SET @sql = @sql + 'CONTAINS(p.[ShortDescription], @Keywords) ' ELSE SET @sql = @sql + 'PATINDEX(@Keywords, p.[ShortDescription]) > 0 ' --product full description SET @sql = @sql + ' UNION SELECT p.Id FROM Product p with (NOLOCK) WHERE ' IF @UseFullTextSearch = 1 SET @sql = @sql + 'CONTAINS(p.[FullDescription], @Keywords) ' ELSE SET @sql = @sql + 'PATINDEX(@Keywords, p.[FullDescription]) > 0 ' --localized product short description SET @sql = @sql + ' UNION SELECT lp.EntityId FROM LocalizedProperty lp with (NOLOCK) WHERE lp.LocaleKeyGroup = N''Product'' AND lp.LanguageId = ' + ISNULL(CAST(@LanguageId AS nvarchar(max)), '0') + ' AND lp.LocaleKey = N''ShortDescription''' IF @UseFullTextSearch = 1 SET @sql = @sql + ' AND CONTAINS(lp.[LocaleValue], @Keywords) ' ELSE SET @sql = @sql + ' AND PATINDEX(@Keywords, lp.[LocaleValue]) > 0 ' --localized product full description SET @sql = @sql + ' UNION SELECT lp.EntityId FROM LocalizedProperty lp with (NOLOCK) WHERE lp.LocaleKeyGroup = N''Product'' AND lp.LanguageId = ' + ISNULL(CAST(@LanguageId AS nvarchar(max)), '0') + ' AND lp.LocaleKey = N''FullDescription''' IF @UseFullTextSearch = 1 SET @sql = @sql + ' AND CONTAINS(lp.[LocaleValue], @Keywords) ' ELSE SET @sql = @sql + ' AND PATINDEX(@Keywords, lp.[LocaleValue]) > 0 ' END --manufacturer part number (exact match) IF @SearchManufacturerPartNumber = 1 BEGIN SET @sql = @sql + ' UNION SELECT p.Id FROM Product p with (NOLOCK) WHERE p.[ManufacturerPartNumber] = @OriginalKeywords ' END --SKU (exact match) IF @SearchSku = 1 BEGIN SET @sql = @sql + ' UNION SELECT p.Id FROM Product p with (NOLOCK) WHERE p.[Sku] = @OriginalKeywords ' END IF @SearchProductTags = 1 BEGIN --product tags (exact match) SET @sql = @sql + ' UNION SELECT pptm.Product_Id FROM Product_ProductTag_Mapping pptm with(NOLOCK) INNER JOIN ProductTag pt with(NOLOCK) ON pt.Id = pptm.ProductTag_Id WHERE pt.[Name] = @OriginalKeywords ' --localized product tags SET @sql = @sql + ' UNION SELECT pptm.Product_Id FROM LocalizedProperty lp with (NOLOCK) INNER JOIN Product_ProductTag_Mapping pptm with(NOLOCK) ON lp.EntityId = pptm.ProductTag_Id WHERE lp.LocaleKeyGroup = N''ProductTag'' AND lp.LanguageId = ' + ISNULL(CAST(@LanguageId AS nvarchar(max)), '0') + ' AND lp.LocaleKey = N''Name'' AND lp.[LocaleValue] = @OriginalKeywords ' END --PRINT (@sql) EXEC sp_executesql @sql, N'@Keywords nvarchar(4000), @OriginalKeywords nvarchar(4000)', @Keywords, @OriginalKeywords END ELSE BEGIN SET @SearchKeywords = 0 END --filter by category IDs SET @CategoryIds = isnull(@CategoryIds, '') CREATE TABLE #FilteredCategoryIds ( CategoryId int not null ) INSERT INTO #FilteredCategoryIds (CategoryId) SELECT CAST(data as int) FROM [nop_splitstring_to_table](@CategoryIds, ',') DECLARE @CategoryIdsCount int SET @CategoryIdsCount = (SELECT COUNT(1) FROM #FilteredCategoryIds) --filter by customer role IDs (access control list) SET @AllowedCustomerRoleIds = isnull(@AllowedCustomerRoleIds, '') CREATE TABLE #FilteredCustomerRoleIds ( CustomerRoleId int not null ) INSERT INTO #FilteredCustomerRoleIds (CustomerRoleId) SELECT CAST(data as int) FROM [nop_splitstring_to_table](@AllowedCustomerRoleIds, ',') DECLARE @FilteredCustomerRoleIdsCount int SET @FilteredCustomerRoleIdsCount = (SELECT COUNT(1) FROM #FilteredCustomerRoleIds) --paging DECLARE @PageLowerBound int DECLARE @PageUpperBound int DECLARE @RowsToReturn int SET @RowsToReturn = @PageSize * (@PageIndex + 1) SET @PageLowerBound = @PageSize * @PageIndex SET @PageUpperBound = @PageLowerBound + @PageSize + 1 CREATE TABLE #DisplayOrderTmp ( [Id] int IDENTITY (1, 1) NOT NULL, [ProductId] int NOT NULL ) SET @sql = ' SELECT p.Id FROM Product p with (NOLOCK)' IF @CategoryIdsCount > 0 BEGIN SET @sql = @sql + ' INNER JOIN Product_Category_Mapping pcm with (NOLOCK) ON p.Id = pcm.ProductId' END IF @ManufacturerId > 0 BEGIN SET @sql = @sql + ' INNER JOIN Product_Manufacturer_Mapping pmm with (NOLOCK) ON p.Id = pmm.ProductId' END IF ISNULL(@ProductTagId, 0) != 0 BEGIN SET @sql = @sql + ' INNER JOIN Product_ProductTag_Mapping pptm with (NOLOCK) ON p.Id = pptm.Product_Id' END --searching by keywords IF @SearchKeywords = 1 BEGIN SET @sql = @sql + ' JOIN #KeywordProducts kp ON p.Id = kp.ProductId' END SET @sql = @sql + ' WHERE p.Deleted = 0' --filter by category IF @CategoryIdsCount > 0 BEGIN SET @sql = @sql + ' AND pcm.CategoryId IN (' SET @sql = @sql + + CAST(@CategoryIds AS nvarchar(max)) SET @sql = @sql + ')' IF @FeaturedProducts IS NOT NULL BEGIN SET @sql = @sql + ' AND pcm.IsFeaturedProduct = ' + CAST(@FeaturedProducts AS nvarchar(max)) END END --filter by manufacturer IF @ManufacturerId > 0 BEGIN SET @sql = @sql + ' AND pmm.ManufacturerId = ' + CAST(@ManufacturerId AS nvarchar(max)) IF @FeaturedProducts IS NOT NULL BEGIN SET @sql = @sql + ' AND pmm.IsFeaturedProduct = ' + CAST(@FeaturedProducts AS nvarchar(max)) END END --filter by vendor IF @VendorId > 0 BEGIN SET @sql = @sql + ' AND p.VendorId = ' + CAST(@VendorId AS nvarchar(max)) END --filter by warehouse IF @WarehouseId > 0 BEGIN --we should also ensure that 'ManageInventoryMethodId' is set to 'ManageStock' (1) --but we skip it in order to prevent hard-coded values (e.g. 1) and for better performance SET @sql = @sql + ' AND ( (p.UseMultipleWarehouses = 0 AND p.WarehouseId = ' + CAST(@WarehouseId AS nvarchar(max)) + ') OR (p.UseMultipleWarehouses > 0 AND EXISTS (SELECT 1 FROM ProductWarehouseInventory [pwi] WHERE [pwi].WarehouseId = ' + CAST(@WarehouseId AS nvarchar(max)) + ' AND [pwi].ProductId = p.Id)) )' END --filter by product type IF @ProductTypeId is not null BEGIN SET @sql = @sql + ' AND p.ProductTypeId = ' + CAST(@ProductTypeId AS nvarchar(max)) END --filter by "visible individually" IF @VisibleIndividuallyOnly = 1 BEGIN SET @sql = @sql + ' AND p.VisibleIndividually = 1' END --filter by "marked as new" IF @MarkedAsNewOnly = 1 BEGIN SET @sql = @sql + ' AND p.MarkAsNew = 1 AND (getutcdate() BETWEEN ISNULL(p.MarkAsNewStartDateTimeUtc, ''1/1/1900'') and ISNULL(p.MarkAsNewEndDateTimeUtc, ''1/1/2999''))' END --filter by product tag IF ISNULL(@ProductTagId, 0) != 0 BEGIN SET @sql = @sql + ' AND pptm.ProductTag_Id = ' + CAST(@ProductTagId AS nvarchar(max)) END --"Published" property IF (@OverridePublished is null) BEGIN --process according to "showHidden" IF @ShowHidden = 0 BEGIN SET @sql = @sql + ' AND p.Published = 1' END END ELSE IF (@OverridePublished = 1) BEGIN --published only SET @sql = @sql + ' AND p.Published = 1' END ELSE IF (@OverridePublished = 0) BEGIN --unpublished only SET @sql = @sql + ' AND p.Published = 0' END --show hidden IF @ShowHidden = 0 BEGIN SET @sql = @sql + ' AND p.Deleted = 0 AND (getutcdate() BETWEEN ISNULL(p.AvailableStartDateTimeUtc, ''1/1/1900'') and ISNULL(p.AvailableEndDateTimeUtc, ''1/1/2999''))' END --min price IF @PriceMin is not null BEGIN SET @sql = @sql + ' AND (p.Price >= ' + CAST(@PriceMin AS nvarchar(max)) + ')' END --max price IF @PriceMax is not null BEGIN SET @sql = @sql + ' AND (p.Price <= ' + CAST(@PriceMax AS nvarchar(max)) + ')' END --show hidden and ACL IF @ShowHidden = 0 and @FilteredCustomerRoleIdsCount > 0 BEGIN SET @sql = @sql + ' AND (p.SubjectToAcl = 0 OR EXISTS ( SELECT 1 FROM #FilteredCustomerRoleIds [fcr] WHERE [fcr].CustomerRoleId IN ( SELECT [acl].CustomerRoleId FROM [AclRecord] acl with (NOLOCK) WHERE [acl].EntityId = p.Id AND [acl].EntityName = ''Product'' ) ))' END --filter by store IF @StoreId > 0 BEGIN SET @sql = @sql + ' AND (p.LimitedToStores = 0 OR EXISTS ( SELECT 1 FROM [StoreMapping] sm with (NOLOCK) WHERE [sm].EntityId = p.Id AND [sm].EntityName = ''Product'' and [sm].StoreId=' + CAST(@StoreId AS nvarchar(max)) + ' ))' END --prepare filterable specification attribute option identifier (if requested) IF @LoadFilterableSpecificationAttributeOptionIds = 1 BEGIN CREATE TABLE #FilterableSpecs ( [SpecificationAttributeOptionId] int NOT NULL ) DECLARE @sql_filterableSpecs nvarchar(max) SET @sql_filterableSpecs = ' INSERT INTO #FilterableSpecs ([SpecificationAttributeOptionId]) SELECT DISTINCT [psam].SpecificationAttributeOptionId FROM [Product_SpecificationAttribute_Mapping] [psam] WITH (NOLOCK) WHERE [psam].[AllowFiltering] = 1 AND [psam].[ProductId] IN (' + @sql + ')' EXEC sp_executesql @sql_filterableSpecs --build comma separated list of filterable identifiers SELECT @FilterableSpecificationAttributeOptionIds = COALESCE(@FilterableSpecificationAttributeOptionIds + ',' , '') + CAST(SpecificationAttributeOptionId as nvarchar(4000)) FROM #FilterableSpecs DROP TABLE #FilterableSpecs END --filter by specification attribution options SET @FilteredSpecs = isnull(@FilteredSpecs, '') CREATE TABLE #FilteredSpecs ( SpecificationAttributeOptionId int not null ) INSERT INTO #FilteredSpecs (SpecificationAttributeOptionId) SELECT CAST(data as int) FROM [nop_splitstring_to_table](@FilteredSpecs, ',') CREATE TABLE #FilteredSpecsWithAttributes ( SpecificationAttributeId int not null, SpecificationAttributeOptionId int not null ) INSERT INTO #FilteredSpecsWithAttributes (SpecificationAttributeId, SpecificationAttributeOptionId) SELECT sao.SpecificationAttributeId, fs.SpecificationAttributeOptionId FROM #FilteredSpecs fs INNER JOIN SpecificationAttributeOption sao ON sao.Id = fs.SpecificationAttributeOptionId ORDER BY sao.SpecificationAttributeId DECLARE @SpecAttributesCount int SET @SpecAttributesCount = (SELECT COUNT(1) FROM #FilteredSpecsWithAttributes) IF @SpecAttributesCount > 0 BEGIN --do it for each specified specification option DECLARE @SpecificationAttributeOptionId int DECLARE @SpecificationAttributeId int DECLARE @LastSpecificationAttributeId int SET @LastSpecificationAttributeId = 0 DECLARE cur_SpecificationAttributeOption CURSOR FOR SELECT SpecificationAttributeId, SpecificationAttributeOptionId FROM #FilteredSpecsWithAttributes OPEN cur_SpecificationAttributeOption FOREACH: FETCH NEXT FROM cur_SpecificationAttributeOption INTO @SpecificationAttributeId, @SpecificationAttributeOptionId IF (@LastSpecificationAttributeId <> 0 AND @SpecificationAttributeId <> @LastSpecificationAttributeId OR @@FETCH_STATUS <> 0) SET @sql = @sql + ' AND p.Id in (select psam.ProductId from [Product_SpecificationAttribute_Mapping] psam with (NOLOCK) where psam.AllowFiltering = 1 and psam.SpecificationAttributeOptionId IN (SELECT SpecificationAttributeOptionId FROM #FilteredSpecsWithAttributes WHERE SpecificationAttributeId = ' + CAST(@LastSpecificationAttributeId AS nvarchar(max)) + '))' SET @LastSpecificationAttributeId = @SpecificationAttributeId IF @@FETCH_STATUS = 0 GOTO FOREACH CLOSE cur_SpecificationAttributeOption DEALLOCATE cur_SpecificationAttributeOption END --sorting SET @sql_orderby = '' IF @OrderBy = 5 /* Name: A to Z */ SET @sql_orderby = ' p.[Name] ASC' ELSE IF @OrderBy = 6 /* Name: Z to A */ SET @sql_orderby = ' p.[Name] DESC' ELSE IF @OrderBy = 10 /* Price: Low to High */ SET @sql_orderby = ' p.[Price] ASC' ELSE IF @OrderBy = 11 /* Price: High to Low */ SET @sql_orderby = ' p.[Price] DESC' ELSE IF @OrderBy = 15 /* creation date */ SET @sql_orderby = ' p.[CreatedOnUtc] DESC' ELSE /* default sorting, 0 (position) */ BEGIN --category position (display order) IF @CategoryIdsCount > 0 SET @sql_orderby = ' pcm.DisplayOrder ASC' --manufacturer position (display order) IF @ManufacturerId > 0 BEGIN IF LEN(@sql_orderby) > 0 SET @sql_orderby = @sql_orderby + ', ' SET @sql_orderby = @sql_orderby + ' pmm.DisplayOrder ASC' END --name IF LEN(@sql_orderby) > 0 SET @sql_orderby = @sql_orderby + ', ' SET @sql_orderby = @sql_orderby + ' p.[Name] ASC' END SET @sql = @sql + ' ORDER BY' + @sql_orderby SET @sql = ' INSERT INTO #DisplayOrderTmp ([ProductId])' + @sql --PRINT (@sql) EXEC sp_executesql @sql DROP TABLE #FilteredCategoryIds DROP TABLE #FilteredSpecs DROP TABLE #FilteredSpecsWithAttributes DROP TABLE #FilteredCustomerRoleIds DROP TABLE #KeywordProducts CREATE TABLE #PageIndex ( [IndexId] int IDENTITY (1, 1) NOT NULL, [ProductId] int NOT NULL ) INSERT INTO #PageIndex ([ProductId]) SELECT ProductId FROM #DisplayOrderTmp GROUP BY ProductId ORDER BY min([Id]) --total records SET @TotalRecords = @@rowcount DROP TABLE #DisplayOrderTmp --return products SELECT TOP (@RowsToReturn) p.* FROM #PageIndex [pi] INNER JOIN Product p with (NOLOCK) on p.Id = [pi].[ProductId] WHERE [pi].IndexId > @PageLowerBound AND [pi].IndexId < @PageUpperBound ORDER BY [pi].IndexId DROP TABLE #PageIndex END GO --new index IF NOT EXISTS (SELECT 1 from sys.indexes WHERE [NAME]=N'IX_QueuedEmail_SentOnUtc_DontSendBeforeDateUtc_Extended' and object_id=object_id(N'[dbo].[QueuedEmail]')) BEGIN CREATE NONCLUSTERED INDEX [IX_QueuedEmail_SentOnUtc_DontSendBeforeDateUtc_Extended] ON QueuedEmail ([SentOnUtc], [DontSendBeforeDateUtc]) INCLUDE ([SentTries]) END GO --new index IF NOT EXISTS (SELECT 1 from sys.indexes WHERE [NAME]=N'IX_Product_VisibleIndividually_Published_Deleted_Extended' and object_id=object_id(N'[dbo].[Product]')) BEGIN CREATE NONCLUSTERED INDEX [IX_Product_VisibleIndividually_Published_Deleted_Extended] ON Product ([VisibleIndividually],[Published],[Deleted]) INCLUDE ([Id],[AvailableStartDateTimeUtc],[AvailableEndDateTimeUtc]) END GO --new index IF NOT EXISTS (SELECT 1 from sys.indexes WHERE [NAME]=N'IX_Category_Deleted_Extended' and object_id=object_id(N'[dbo].[Category]')) BEGIN CREATE NONCLUSTERED INDEX [IX_Category_Deleted_Extended] ON Category ([Deleted]) INCLUDE ([Id],[Name],[SubjectToAcl],[LimitedToStores],[Published]) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'customersettings.usernamevalidationrule') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'customersettings.usernamevalidationrule', N'', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'ordersettings.deletegiftcardusagehistory') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'ordersettings.deletegiftcardusagehistory', N'False', 0) END GO --update [sename] column for product tags IF EXISTS ( SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[temp_generate_sename]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1) DROP PROCEDURE [temp_generate_sename] GO CREATE PROCEDURE [temp_generate_sename] ( @table_name nvarchar(1000), @entity_id int, @language_id int = 0, --0 to process main sename column, --language id to process a localized value @result nvarchar(1000) OUTPUT ) AS BEGIN --get current name DECLARE @current_sename nvarchar(1000) DECLARE @sql nvarchar(4000) IF (@language_id = 0) BEGIN SET @sql = 'SELECT @current_sename = [Name] FROM [' + @table_name + '] WHERE [Id] = ' + ISNULL(CAST(@entity_id AS nvarchar(max)), '0') EXEC sp_executesql @sql,N'@current_sename nvarchar(1000) OUTPUT',@current_sename OUTPUT END ELSE BEGIN SET @sql = 'SELECT @current_sename = [LocaleValue] FROM [LocalizedProperty] WHERE [LocaleKeyGroup]=''' + @table_name + ''' AND [LocaleKey] = ''Name'' AND [LanguageId] = ' + ISNULL(CAST(@language_id AS nvarchar(max)), '0') + ' AND [EntityId] = ' + ISNULL(CAST(@entity_id AS nvarchar(max)), '0') EXEC sp_executesql @sql,N'@current_sename nvarchar(1000) OUTPUT',@current_sename OUTPUT --if not empty, se name is already specified by a store owner. if empty, we should use poduct name IF (@current_sename is null or @current_sename = N'') BEGIN SET @sql = 'SELECT @current_sename = [LocaleValue] FROM [LocalizedProperty] WHERE [LocaleKeyGroup]=''' + @table_name + ''' AND [LocaleKey] = ''Name'' AND [LanguageId] = ' + ISNULL(CAST(@language_id AS nvarchar(max)), '0') + ' AND [EntityId] = ' + ISNULL(CAST(@entity_id AS nvarchar(max)), '0') EXEC sp_executesql @sql,N'@current_sename nvarchar(1000) OUTPUT',@current_sename OUTPUT END --if localized product name is also empty, we exit IF (@current_sename is null or @current_sename = N'') RETURN END --generate se name DECLARE @new_sename nvarchar(1000) SET @new_sename = '' --ensure only allowed chars DECLARE @allowed_se_chars nvarchar(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(@current_sename) DECLARE @p int SET @p = 1 WHILE @p <= @l BEGIN DECLARE @c nvarchar(1) SET @c = substring(@current_sename, @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 AND NOT ([EntityId] = ' + ISNULL(CAST(@entity_id AS nvarchar(max)), '0') + ' AND [EntityName] = ''' + @table_name + ''')) 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 + '-2' END ELSE BEGIN BREAK END END --return SET @result = @new_sename END GO BEGIN DECLARE @sename_existing_entity_id int DECLARE cur_sename_existing_entity CURSOR FOR SELECT [Id] FROM [ProductTag] 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'ProductTag' DECLARE @product_tag_system_name nvarchar(1000) SET @product_tag_system_name = null -- clear cache (variable scope) SELECT @product_tag_system_name = [Name] FROM [ProductTag] WHERE [Id] = @sename_existing_entity_id --main sename EXEC [dbo].[temp_generate_sename] @table_name = @table_name, @entity_id = @sename_existing_entity_id, @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 --localized values 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 SET @sename = null -- clear cache (variable scope) EXEC [dbo].[temp_generate_sename] @table_name = @table_name, @entity_id = @sename_existing_entity_id, @language_id = @ExistingLanguageID, @result = @sename OUTPUT IF (len(@sename) > 0) BEGIN DECLARE @sql nvarchar(4000) SET @sql = 'IF EXISTS (SELECT 1 FROM [UrlRecord] WHERE [EntityName]=''' + @table_name + ''' AND [LanguageId] = ' + ISNULL(CAST(@ExistingLanguageID AS nvarchar(max)), '0') + ' AND [EntityId] = ' + ISNULL(CAST(@sename_existing_entity_id AS nvarchar(max)), '0') + ') BEGIN --update UPDATE [UrlRecord] SET [Slug] = @sename WHERE [EntityName]=''' + @table_name + ''' AND [LanguageId] = ' + ISNULL(CAST(@ExistingLanguageID AS nvarchar(max)), '0') + ' AND [EntityId] = ' + ISNULL(CAST(@sename_existing_entity_id AS nvarchar(max)), '0') + ' END ELSE BEGIN --insert INSERT INTO [UrlRecord] ([EntityId], [EntityName], [Slug], [IsActive], [LanguageId]) VALUES (' + ISNULL(CAST(@sename_existing_entity_id AS nvarchar(max)), '0') +','''+ @table_name + ''',@sename, 1, ' + ISNULL(CAST(@ExistingLanguageID AS nvarchar(max)), '0')+ ') END ' EXEC sp_executesql @sql,N'@sename nvarchar(1000) OUTPUT',@sename OUTPUT END --fetch next language identifier FETCH NEXT FROM cur_existinglanguage INTO @ExistingLanguageID END CLOSE cur_existinglanguage DEALLOCATE cur_existinglanguage --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_generate_sename]') AND OBJECTPROPERTY(object_id,N'IsProcedure') = 1) DROP PROCEDURE [temp_generate_sename] GO --delete setting DELETE FROM [Setting] WHERE [Name] = N'captchasettings.recaptchaversion' GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'rewardpointssettings.maximumrewardpointstouseperorder') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'rewardpointssettings.maximumrewardpointstouseperorder', N'0', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'catalogsettings.exportimportrelatedentitiesbyname') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'catalogsettings.exportimportrelatedentitiesbyname', N'true', 0) END GO --delete setting DELETE FROM [Setting] WHERE [Name] = N'commonsettings.usestoredproceduresifsupported' GO --drop some indexes IF EXISTS (SELECT 1 from sys.indexes WHERE [NAME]=N'IX_PMM_ProductId' and object_id=object_id(N'[Product_Manufacturer_Mapping]')) BEGIN DROP INDEX [IX_PMM_ProductId] ON [Product_Manufacturer_Mapping] END GO IF EXISTS (SELECT 1 from sys.indexes WHERE [NAME]=N'IX_PCM_ProductId' and object_id=object_id(N'[Product_Category_Mapping]')) BEGIN DROP INDEX [IX_PCM_ProductId] ON [Product_Category_Mapping] END GO IF EXISTS (SELECT 1 from sys.indexes WHERE [NAME]=N'IX_PSAM_ProductId' and object_id=object_id(N'[Product_SpecificationAttribute_Mapping]')) BEGIN DROP INDEX [IX_PSAM_ProductId] ON [Product_SpecificationAttribute_Mapping] END GO --update the FullText_IsSupported procedure ALTER PROCEDURE [dbo].[FullText_IsSupported] AS BEGIN EXEC(' SELECT CASE SERVERPROPERTY(''IsFullTextInstalled'') WHEN 1 THEN CASE DatabaseProperty (DB_NAME(DB_ID()), ''IsFulltextEnabled'') WHEN 1 THEN 1 ELSE 0 END ELSE 0 END as Value') END GO --update setting UPDATE [Setting] SET [Value] = N' ' WHERE [Name] = N'googleanalyticssettings.trackingscript' GO --GDPR consent IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = object_id(N'[GdprConsent]') AND objectproperty(object_id, N'IsUserTable') = 1) BEGIN CREATE TABLE [dbo].[GdprConsent] ( [Id] INT IDENTITY(1,1) NOT NULL, [Message] NVARCHAR(MAX) NOT NULL, [IsRequired] BIT NOT NULL, [RequiredMessage] NVARCHAR(MAX) NULL, [DisplayDuringRegistration] BIT NOT NULL, [DisplayOnCustomerInfoPage] 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 --GDPR log IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = object_id(N'[GdprLog]') AND objectproperty(object_id, N'IsUserTable') = 1) BEGIN CREATE TABLE [dbo].[GdprLog] ( [Id] INT IDENTITY(1,1) NOT NULL, [CustomerId] INT NOT NULL, [ConsentId] INT NOT NULL, [CustomerInfo] NVARCHAR(MAX) NOT NULL, [RequestTypeId] INT NOT NULL, [RequestDetails] NVARCHAR(MAX) NOT NULL, [CreatedOnUtc] DATETIME 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 --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'gdprsettings.gdprenabled') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'gdprsettings.gdprenabled', N'false', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'gdprsettings.logprivacypolicyconsent') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'gdprsettings.logprivacypolicyconsent', N'true', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'gdprsettings.lognewsletterconsent') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'gdprsettings.lognewsletterconsent', N'true', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'catalogsettings.showlinktoallresultinsearchautocomplete') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'catalogsettings.showlinktoallresultinsearchautocomplete', N'false', 0) END GO --rename setting UPDATE [Setting] SET [Name] = 'captchasettings.recaptchadefaultlanguage' WHERE [Name] = 'captchasettings.recaptchalanguage' --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'captchasettings.recaptchadefaultlanguage') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'captchasettings.recaptchadefaultlanguage', N'', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'catalogsettings.countdisplayedyearsdatepicker') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'catalogsettings.countdisplayedyearsdatepicker', N'1', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'captchasettings.automaticallychooselanguage') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'captchasettings.automaticallychooselanguage', N'True', 0) END GO --new table IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PictureBinary]') and OBJECTPROPERTY(object_id, N'IsUserTable') = 1) BEGIN EXEC('CREATE TABLE [dbo].[PictureBinary] ( [Id] int IDENTITY(1,1) NOT NULL, [PictureId] int NOT NULL, [BinaryData] [varbinary](max) 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) ) --copy existing data INSERT INTO [dbo].[PictureBinary](PictureId, BinaryData) SELECT [Id], [PictureBinary] FROM [dbo].[Picture] ALTER TABLE dbo.Picture DROP COLUMN [PictureBinary] ALTER INDEX ALL ON [Picture] REBUILD') END GO IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'FK_PictureBinary_Picture_PictureId') AND parent_object_id = OBJECT_ID(N'PictureBinary')) ALTER TABLE [PictureBinary] DROP CONSTRAINT [FK_PictureBinary_Picture_PictureId] GO ALTER TABLE [dbo].[PictureBinary] WITH CHECK ADD CONSTRAINT [FK_PictureBinary_Picture_PictureId] FOREIGN KEY(PictureId) REFERENCES [dbo].[Picture] ([Id]) ON DELETE CASCADE GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'catalogsettings.displaydatepreorderavailability') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'catalogsettings.displaydatepreorderavailability', N'False', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'adminareasettings.richeditorallowstyletag') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'adminareasettings.richeditorallowstyletag', N'False', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'customersettings.allowcustomerstocheckgiftcardbalance') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'customersettings.allowcustomerstocheckgiftcardbalance', N'false', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'adminareasettings.checkcopyrightremovalkey') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'adminareasettings.checkcopyrightremovalkey', N'true', 0) END GO --Review type IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = object_id(N'[ReviewType]') AND objectproperty(object_id, N'IsUserTable') = 1) BEGIN CREATE TABLE [dbo].[ReviewType]( [Id] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](max) NOT NULL, [Description] [nvarchar](max) NOT NULL, [DisplayOrder] [int] NOT NULL, [VisibleToAllCustomers] [bit] NOT NULL, [IsRequired] [bit] NOT NULL, PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] END GO --Product review and review type mapping IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = object_id(N'[ProductReview_ReviewType_Mapping]') AND objectproperty(object_id, N'IsUserTable') = 1) BEGIN CREATE TABLE [dbo].[ProductReview_ReviewType_Mapping]( [Id] [int] IDENTITY(1,1) NOT NULL, [ProductReviewID] [int] NOT NULL, [ReviewTypeID] [int] NOT NULL, [Rating] [int] NOT NULL, PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY] ) ON [PRIMARY] ALTER TABLE [dbo].[ProductReview_ReviewType_Mapping] WITH CHECK ADD CONSTRAINT [ProductReviewReviewTypeRel_ProductReview] FOREIGN KEY([ProductReviewID]) REFERENCES [dbo].[ProductReview] ([Id]) ON DELETE CASCADE ALTER TABLE [dbo].[ProductReview_ReviewType_Mapping] CHECK CONSTRAINT [ProductReviewReviewTypeRel_ProductReview] ALTER TABLE [dbo].[ProductReview_ReviewType_Mapping] WITH CHECK ADD CONSTRAINT [ProductReviewReviewTypeRel_ReviewType] FOREIGN KEY([ReviewTypeID]) REFERENCES [dbo].[ReviewType] ([Id]) ON DELETE CASCADE ALTER TABLE [dbo].[ProductReview_ReviewType_Mapping] CHECK CONSTRAINT [ProductReviewReviewTypeRel_ReviewType] END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'commonsettings.jquerymigratescriptloggingactive') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'commonsettings.jquerymigratescriptloggingactive', N'False', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [Name] = N'commonsettings.supportpreviousnopcommerceversions') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'commonsettings.supportpreviousnopcommerceversions', N'true', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [Name] = N'commonsettings.useresponsecompression') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'commonsettings.useresponsecompression', N'false', 0) END GO --new setting IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [Name] = N'commonsettings.staticfilescachecontrol') BEGIN INSERT [Setting] ([Name], [Value], [StoreId]) VALUES (N'commonsettings.staticfilescachecontrol', N'public,max-age=604800', 0) END GO --rename table IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ShippingByWeightByTotal]') and OBJECTPROPERTY(object_id, N'IsUserTable') = 1) BEGIN EXEC sp_RENAME '[dbo].[ShippingByWeightByTotal]', 'ShippingByWeightByTotalRecord' END GO