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
--SKU
IF @SearchSku = 1
BEGIN
SET @sql = @sql + '
UNION
SELECT p.Id
FROM Product p with (NOLOCK)
WHERE '
IF @UseFullTextSearch = 1
SET @sql = @sql + 'CONTAINS(p.[Sku], @Keywords) '
ELSE
SET @sql = @sql + 'PATINDEX(@Keywords, p.[Sku]) > 0 '
END
IF @SearchProductTags = 1
BEGIN
--product tag
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 '
IF @UseFullTextSearch = 1
SET @sql = @sql + 'CONTAINS(pt.[Name], @Keywords) '
ELSE
SET @sql = @sql + 'PATINDEX(@Keywords, pt.[Name]) > 0 '
--localized product tag
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'''
IF @UseFullTextSearch = 1
SET @sql = @sql + ' AND CONTAINS(lp.[LocaleValue], @Keywords) '
ELSE
SET @sql = @sql + ' AND PATINDEX(@Keywords, lp.[LocaleValue]) > 0 '
END
--PRINT (@sql)
EXEC sp_executesql @sql, N'@Keywords nvarchar(4000)', @Keywords
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 attributes
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, ',')
DECLARE @SpecAttributesCount int
SET @SpecAttributesCount = (SELECT COUNT(1) FROM #FilteredSpecs)
--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, ',')
--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 = '
INSERT INTO #DisplayOrderTmp ([ProductId])
SELECT p.Id
FROM
Product p with (NOLOCK)'
IF @CategoryIdsCount > 0
BEGIN
SET @sql = @sql + '
LEFT JOIN Product_Category_Mapping pcm with (NOLOCK)
ON p.Id = pcm.ProductId'
END
IF @ManufacturerId > 0
BEGIN
SET @sql = @sql + '
LEFT JOIN Product_Manufacturer_Mapping pmm with (NOLOCK)
ON p.Id = pmm.ProductId'
END
IF ISNULL(@ProductTagId, 0) != 0
BEGIN
SET @sql = @sql + '
LEFT 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 (SELECT CategoryId FROM #FilteredCategoryIds)'
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 parent grouped product identifer
IF @ParentGroupedProductId > 0
BEGIN
SET @sql = @sql + '
AND p.ParentGroupedProductId = ' + CAST(@ParentGroupedProductId AS nvarchar(max))
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 parent product identifer
IF @VisibleIndividuallyOnly = 1
BEGIN
SET @sql = @sql + '
AND p.VisibleIndividually = 1'
END
--filter by product tag
IF ISNULL(@ProductTagId, 0) != 0
BEGIN
SET @sql = @sql + '
AND pptm.ProductTag_Id = ' + CAST(@ProductTagId AS nvarchar(max))
END
--show hidden
IF @ShowHidden = 0
BEGIN
SET @sql = @sql + '
AND p.Published = 1
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 (
(
--special price (specified price and valid date range)
(p.SpecialPrice IS NOT NULL AND (getutcdate() BETWEEN isnull(p.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(p.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
AND
(p.SpecialPrice >= ' + CAST(@PriceMin AS nvarchar(max)) + ')
)
OR
(
--regular price (price isnt specified or date range isnt valid)
(p.SpecialPrice IS NULL OR (getutcdate() NOT BETWEEN isnull(p.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(p.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
AND
(p.Price >= ' + CAST(@PriceMin AS nvarchar(max)) + ')
)
)'
END
--max price
IF @PriceMax is not null
BEGIN
SET @sql = @sql + '
AND (
(
--special price (specified price and valid date range)
(p.SpecialPrice IS NOT NULL AND (getutcdate() BETWEEN isnull(p.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(p.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
AND
(p.SpecialPrice <= ' + CAST(@PriceMax AS nvarchar(max)) + ')
)
OR
(
--regular price (price isnt specified or date range isnt valid)
(p.SpecialPrice IS NULL OR (getutcdate() NOT BETWEEN isnull(p.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(p.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
AND
(p.Price <= ' + CAST(@PriceMax AS nvarchar(max)) + ')
)
)'
END
--show hidden and ACL
IF @ShowHidden = 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
--show hidden and 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
--filter by specs
IF @SpecAttributesCount > 0
BEGIN
SET @sql = @sql + '
AND NOT EXISTS (
SELECT 1 FROM #FilteredSpecs [fs]
WHERE
[fs].SpecificationAttributeOptionId NOT IN (
SELECT psam.SpecificationAttributeOptionId
FROM Product_SpecificationAttribute_Mapping psam with (NOLOCK)
WHERE psam.AllowFiltering = 1 AND psam.ProductId = p.Id
)
)'
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
--parent grouped product specified (sort associated products)
IF @ParentGroupedProductId > 0
BEGIN
IF LEN(@sql_orderby) > 0 SET @sql_orderby = @sql_orderby + ', '
SET @sql_orderby = @sql_orderby + ' p.[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
--PRINT (@sql)
EXEC sp_executesql @sql
DROP TABLE #FilteredCategoryIds
DROP TABLE #FilteredSpecs
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
--prepare filterable specification attribute option identifier (if requested)
IF @LoadFilterableSpecificationAttributeOptionIds = 1
BEGIN
CREATE TABLE #FilterableSpecs
(
[SpecificationAttributeOptionId] int NOT NULL
)
INSERT INTO #FilterableSpecs ([SpecificationAttributeOptionId])
SELECT DISTINCT [psam].SpecificationAttributeOptionId
FROM [Product_SpecificationAttribute_Mapping] [psam] with (NOLOCK)
WHERE [psam].[AllowFiltering] = 1
AND [psam].[ProductId] IN (SELECT [pi].ProductId FROM #PageIndex [pi])
--build comma separated list of filterable identifiers
SELECT @FilterableSpecificationAttributeOptionIds = COALESCE(@FilterableSpecificationAttributeOptionIds + ',' , '') + CAST(SpecificationAttributeOptionId as nvarchar(4000))
FROM #FilterableSpecs
DROP TABLE #FilterableSpecs
END
--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 column
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[Warehouse]') and NAME='AdminComment')
BEGIN
ALTER TABLE [Warehouse]
ADD [AdminComment] nvarchar(MAX) NULL
END
GO
--new column
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[ProductWarehouseInventory]') and NAME='ReservedQuantity')
BEGIN
ALTER TABLE [ProductWarehouseInventory]
ADD [ReservedQuantity] int NULL
END
GO
UPDATE [ProductWarehouseInventory]
SET [ReservedQuantity] = 0
WHERE [ReservedQuantity] IS NULL
GO
ALTER TABLE [ProductWarehouseInventory] ALTER COLUMN [ReservedQuantity] int NOT NULL
GO
--new setting
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'catalogsettings.loadallsidecategorymenusubcategories')
BEGIN
INSERT [Setting] ([Name], [Value], [StoreId])
VALUES (N'catalogsettings.loadallsidecategorymenusubcategories', N'false', 0)
END
GO
--new column
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[Product_SpecificationAttribute_Mapping]') and NAME='AttributeTypeId')
BEGIN
ALTER TABLE [Product_SpecificationAttribute_Mapping]
ADD [AttributeTypeId] int NULL
END
GO
--"custom text" attribute type (if "CustomValue" column is specified)
UPDATE [Product_SpecificationAttribute_Mapping]
SET [AttributeTypeId] = 10
WHERE [AttributeTypeId] IS NULL AND LEN([CustomValue]) > 0
GO
UPDATE [Product_SpecificationAttribute_Mapping]
SET [AttributeTypeId] = 0
WHERE [AttributeTypeId] IS NULL
GO
ALTER TABLE [Product_SpecificationAttribute_Mapping] ALTER COLUMN [AttributeTypeId] int NOT NULL
GO
--new setting
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'catalogsettings.displaytaxshippinginfowishlist')
BEGIN
INSERT [Setting] ([Name], [Value], [StoreId])
VALUES (N'catalogsettings.displaytaxshippinginfowishlist', N'false', 0)
END
GO
--new setting
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'catalogsettings.displaytaxshippinginfoorderdetailspage')
BEGIN
INSERT [Setting] ([Name], [Value], [StoreId])
VALUES (N'catalogsettings.displaytaxshippinginfoorderdetailspage', N'false', 0)
END
GO
--new column
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[Store]') and NAME='CompanyName')
BEGIN
ALTER TABLE [Store]
ADD [CompanyName] nvarchar(1000) NULL
END
GO
--new column
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[Store]') and NAME='CompanyAddress')
BEGIN
ALTER TABLE [Store]
ADD [CompanyAddress] nvarchar(1000) NULL
END
GO
--new column
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[Store]') and NAME='CompanyPhoneNumber')
BEGIN
ALTER TABLE [Store]
ADD [CompanyPhoneNumber] nvarchar(1000) NULL
END
GO
--new column
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[Store]') and NAME='CompanyVat')
BEGIN
ALTER TABLE [Store]
ADD [CompanyVat] nvarchar(1000) NULL
END
GO
--'Order paid' message template
IF NOT EXISTS (
SELECT 1
FROM [MessageTemplate]
WHERE [Name] = N'OrderPaid.CustomerNotification')
BEGIN
INSERT [MessageTemplate] ([Name], [BccEmailAddresses], [Subject], [Body], [IsActive], [EmailAccountId], [LimitedToStores])
VALUES (N'OrderPaid.CustomerNotification', null, N'%Store.Name%. Order #%Order.OrderNumber% paid', N'%Store.Name%
Hello %Order.CustomerFullName%,
Thanks for buying from %Store.Name%. Order #%Order.OrderNumber% has been just paid. Below is the summary of the order.
Order Number: %Order.OrderNumber%
Order Details: %Order.OrderURLForCustomer%
Date Ordered: %Order.CreatedOn%
Billing Address
%Order.BillingFirstName% %Order.BillingLastName%
%Order.BillingAddress1%
%Order.BillingCity% %Order.BillingZipPostalCode%
%Order.BillingStateProvince% %Order.BillingCountry%
Shipping Address
%Order.ShippingFirstName% %Order.ShippingLastName%
%Order.ShippingAddress1%
%Order.ShippingCity% %Order.ShippingZipPostalCode%
%Order.ShippingStateProvince% %Order.ShippingCountry%
Shipping Method: %Order.ShippingMethod%
%Order.Product(s)%
', 0, 0, 0)
END
GO
--delete a setting
DELETE FROM [Setting]
WHERE [name] = N'commonsettings.sitemapincludetopics'
GO
--'Order paid' message template
IF NOT EXISTS (
SELECT 1
FROM [MessageTemplate]
WHERE [Name] = N'OrderPaid.VendorNotification')
BEGIN
INSERT [MessageTemplate] ([Name], [BccEmailAddresses], [Subject], [Body], [IsActive], [EmailAccountId], [LimitedToStores])
VALUES (N'OrderPaid.VendorNotification', null, N'%Store.Name%. Order #%Order.OrderNumber% paid', N'%Store.Name%
Order #%Order.OrderNumber% has been just paid.
Order Number: %Order.OrderNumber%
Date Ordered: %Order.CreatedOn%
%Order.Product(s)%
', 0, 0, 0)
END
GO
--new setting
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'adminareasettings.richeditoradditionalsettings')
BEGIN
INSERT [Setting] ([Name], [Value], [StoreId])
VALUES (N'adminareasettings.richeditoradditionalsettings', N'', 0)
END
GO
--new setting
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'shippingsettings.pickupinstorefee')
BEGIN
INSERT [Setting] ([Name], [Value], [StoreId])
VALUES (N'shippingsettings.pickupinstorefee', N'0', 0)
END
GO
--new column
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[ProductVariantAttributeCombination]') and OBJECTPROPERTY(object_id, N'IsUserTable') = 1)
BEGIN
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[ProductVariantAttributeCombination]') and NAME='NotifyAdminForQuantityBelow')
BEGIN
EXEC ('ALTER TABLE [ProductVariantAttributeCombination] ADD [NotifyAdminForQuantityBelow] int NULL')
EXEC ('UPDATE [ProductVariantAttributeCombination] SET [NotifyAdminForQuantityBelow] = 1 WHERE [NotifyAdminForQuantityBelow] IS NULL')
EXEC ('ALTER TABLE [ProductVariantAttributeCombination] ALTER COLUMN [NotifyAdminForQuantityBelow] int NOT NULL')
END
END
GO
--'Quantity below' message template for attribute combinations
IF NOT EXISTS (
SELECT 1
FROM [MessageTemplate]
WHERE [Name] = N'QuantityBelow.AttributeCombination.StoreOwnerNotification')
BEGIN
INSERT [MessageTemplate] ([Name], [BccEmailAddresses], [Subject], [Body], [IsActive], [EmailAccountId], [LimitedToStores])
VALUES (N'QuantityBelow.AttributeCombination.StoreOwnerNotification', null, N'%Store.Name%. Quantity below notification. %Product.Name%', N'%Store.Name%
%Product.Name% (ID: %Product.ID%) low quantity.
%AttributeCombination.Formatted%
Quantity: %AttributeCombination.StockQuantity%
', 1, 0, 0)
END
GO
--address attributes
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[AddressAttribute]') and OBJECTPROPERTY(object_id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[AddressAttribute](
[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'[AddressAttributeValue]') and OBJECTPROPERTY(object_id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[AddressAttributeValue](
[Id] [int] IDENTITY(1,1) NOT NULL,
[AddressAttributeId] [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 = 'AddressAttributeValue_AddressAttribute'
AND parent_object_id = Object_id('AddressAttributeValue')
AND Objectproperty(object_id,N'IsForeignKey') = 1)
ALTER TABLE dbo.AddressAttributeValue
DROP CONSTRAINT AddressAttributeValue_AddressAttribute
GO
ALTER TABLE [dbo].[AddressAttributeValue] WITH CHECK ADD CONSTRAINT [AddressAttributeValue_AddressAttribute] FOREIGN KEY([AddressAttributeId])
REFERENCES [dbo].[AddressAttribute] ([Id])
ON DELETE CASCADE
GO
--new column
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[Address]') and NAME='CustomAttributes')
BEGIN
ALTER TABLE [Address]
ADD [CustomAttributes] nvarchar(MAX) NULL
END
GO
--new column
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[Product]') and NAME='IsRental')
BEGIN
ALTER TABLE [Product]
ADD [IsRental] bit NULL
END
GO
UPDATE [Product]
SET [IsRental] = 0
WHERE [IsRental] IS NULL
GO
ALTER TABLE [Product] ALTER COLUMN [IsRental] bit NOT NULL
GO
--new column
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[Product]') and NAME='RentalPriceLength')
BEGIN
ALTER TABLE [Product]
ADD [RentalPriceLength] int NULL
END
GO
UPDATE [Product]
SET [RentalPriceLength] = 0
WHERE [RentalPriceLength] IS NULL
GO
ALTER TABLE [Product] ALTER COLUMN [RentalPriceLength] int NOT NULL
GO
--new column
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[Product]') and NAME='RentalPricePeriodId')
BEGIN
ALTER TABLE [Product]
ADD [RentalPricePeriodId] bit NULL
END
GO
UPDATE [Product]
SET [RentalPricePeriodId] = 0
WHERE [RentalPricePeriodId] IS NULL
GO
ALTER TABLE [Product] ALTER COLUMN [RentalPricePeriodId] int NOT NULL
GO
--new column
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[ShoppingCartItem]') and NAME='RentalStartDateUtc')
BEGIN
ALTER TABLE [ShoppingCartItem]
ADD [RentalStartDateUtc] datetime NULL
END
GO
--new column
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[ShoppingCartItem]') and NAME='RentalEndDateUtc')
BEGIN
ALTER TABLE [ShoppingCartItem]
ADD [RentalEndDateUtc] datetime NULL
END
GO
--new column
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[OrderItem]') and NAME='RentalStartDateUtc')
BEGIN
ALTER TABLE [OrderItem]
ADD [RentalStartDateUtc] datetime NULL
END
GO
--new column
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[OrderItem]') and NAME='RentalEndDateUtc')
BEGIN
ALTER TABLE [OrderItem]
ADD [RentalEndDateUtc] datetime NULL
END
GO
--drop 'Purchase order' column
IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[Order]') and NAME='PurchaseOrderNumber')
BEGIN
--move existing PurchaseOrderNumber column to CustomValuesXml
UPDATE [Order]
SET [CustomValuesXml] = N'- PO Number' + [PurchaseOrderNumber] + N'
'
WHERE [PaymentMethodSystemName] = N'Payments.PurchaseOrder' and ([CustomValuesXml] is null or [CustomValuesXml] = N'')
EXEC ('ALTER TABLE [Order] DROP COLUMN [PurchaseOrderNumber]')
END
GO
--rename ProductVariantAttributeCombination to ProductAttributeCombination
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[ProductVariantAttributeCombination]') and OBJECTPROPERTY(object_id, N'IsUserTable') = 1)
BEGIN
EXEC sp_rename 'ProductVariantAttributeCombination', 'ProductAttributeCombination';
END
GO
IF EXISTS (SELECT 1
FROM sys.objects
WHERE name = 'ProductVariantAttributeCombination_Product'
AND parent_object_id = Object_id('ProductAttributeCombination')
AND Objectproperty(object_id,N'IsForeignKey') = 1)
BEGIN
EXEC sp_rename 'ProductVariantAttributeCombination_Product', 'ProductAttributeCombination_Product';
END
GO
--new column
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[Product]') and NAME='IsTelecommunicationsOrBroadcastingOrElectronicServices')
BEGIN
ALTER TABLE [Product]
ADD [IsTelecommunicationsOrBroadcastingOrElectronicServices] bit NULL
END
GO
UPDATE [Product]
SET [IsTelecommunicationsOrBroadcastingOrElectronicServices] = 0
WHERE [IsTelecommunicationsOrBroadcastingOrElectronicServices] IS NULL
GO
ALTER TABLE [Product] ALTER COLUMN [IsTelecommunicationsOrBroadcastingOrElectronicServices] bit NOT NULL
GO
--rename column
IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[ProductVariantAttributeValue]') and NAME='ProductVariantAttributeId')
BEGIN
EXEC sp_rename 'ProductVariantAttributeValue.ProductVariantAttributeId', 'ProductAttributeMappingId', 'COLUMN';
END
GO
--rename index
IF EXISTS (SELECT 1 from sys.indexes WHERE [NAME]=N'IX_ProductVariantAttributeValue_ProductVariantAttributeId' and object_id=object_id(N'[ProductVariantAttributeValue]'))
BEGIN
EXEC sp_rename 'ProductVariantAttributeValue.IX_ProductVariantAttributeValue_ProductVariantAttributeId', 'IX_ProductAttributeValue_ProductAttributeMappingId', 'INDEX';
END
GO
IF EXISTS (SELECT 1
FROM sys.objects
WHERE name = 'ProductVariantAttributeValue_ProductVariantAttribute'
AND parent_object_id = Object_id('ProductVariantAttributeValue')
AND Objectproperty(object_id,N'IsForeignKey') = 1)
BEGIN
EXEC sp_rename 'ProductVariantAttributeValue_ProductVariantAttribute', 'ProductAttributeValue_ProductAttributeMapping';
END
GO
--rename table
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[ProductVariantAttributeValue]') and OBJECTPROPERTY(object_id, N'IsUserTable') = 1)
BEGIN
EXEC sp_rename 'ProductVariantAttributeValue', 'ProductAttributeValue';
END
GO
IF EXISTS (SELECT 1
FROM sys.objects
WHERE name = 'ProductVariantAttribute_Product'
AND parent_object_id = Object_id('Product_ProductAttribute_Mapping')
AND Objectproperty(object_id,N'IsForeignKey') = 1)
BEGIN
EXEC sp_rename 'ProductVariantAttribute_Product', 'ProductAttributeMapping_Product';
END
GO
IF EXISTS (SELECT 1
FROM sys.objects
WHERE name = 'ProductVariantAttribute_ProductAttribute'
AND parent_object_id = Object_id('Product_ProductAttribute_Mapping')
AND Objectproperty(object_id,N'IsForeignKey') = 1)
BEGIN
EXEC sp_rename 'ProductVariantAttribute_ProductAttribute', 'ProductAttributeMapping_ProductAttribute';
END
GO
--new column
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[MessageTemplate]') and NAME='AttachedDownloadId')
BEGIN
ALTER TABLE [MessageTemplate]
ADD [AttachedDownloadId] int NULL
END
GO
UPDATE [MessageTemplate]
SET [AttachedDownloadId] = 0
WHERE [AttachedDownloadId] IS NULL
GO
ALTER TABLE [MessageTemplate] ALTER COLUMN [AttachedDownloadId] int NOT NULL
GO
--new column
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[QueuedEmail]') and NAME='AttachedDownloadId')
BEGIN
ALTER TABLE [QueuedEmail]
ADD [AttachedDownloadId] int NULL
END
GO
UPDATE [QueuedEmail]
SET [AttachedDownloadId] = 0
WHERE [AttachedDownloadId] IS NULL
GO
ALTER TABLE [QueuedEmail] ALTER COLUMN [AttachedDownloadId] int NOT NULL
GO
--new stored procedure
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[DeleteGuests]') AND OBJECTPROPERTY(object_id,N'IsProcedure') = 1)
DROP PROCEDURE [DeleteGuests]
GO
CREATE PROCEDURE [dbo].[DeleteGuests]
(
@OnlyWithoutShoppingCart bit = 1,
@CreatedFromUtc datetime,
@CreatedToUtc datetime,
@TotalRecordsDeleted int = null OUTPUT
)
AS
BEGIN
CREATE TABLE #tmp_guests (CustomerId int)
INSERT #tmp_guests (CustomerId)
SELECT [Id] FROM [Customer] c
WHERE
--created from
((@CreatedFromUtc is null) OR (c.[CreatedOnUtc] > @CreatedFromUtc))
AND
--created to
((@CreatedToUtc is null) OR (c.[CreatedOnUtc] < @CreatedToUtc))
AND
--shopping cart items
((@OnlyWithoutShoppingCart=0) OR (NOT EXISTS(SELECT 1 FROM [ShoppingCartItem] sci inner join [Customer] on sci.[CustomerId]=c.[Id])))
AND
--guests only
(EXISTS(SELECT 1 FROM [Customer_CustomerRole_Mapping] ccrm inner join [Customer] on ccrm.[Customer_Id]=c.[Id] inner join [CustomerRole] cr on cr.[Id]=ccrm.[CustomerRole_Id] WHERE cr.[SystemName] = N'Guests'))
AND
--no orders
(NOT EXISTS(SELECT 1 FROM [Order] o inner join [Customer] on o.[CustomerId]=c.[Id]))
AND
--no blog comments
(NOT EXISTS(SELECT 1 FROM [BlogComment] bc inner join [Customer] on bc.[CustomerId]=c.[Id]))
AND
--no news comments
(NOT EXISTS(SELECT 1 FROM [NewsComment] nc inner join [Customer] on nc.[CustomerId]=c.[Id]))
AND
--no product reviews
(NOT EXISTS(SELECT 1 FROM [ProductReview] pr inner join [Customer] on pr.[CustomerId]=c.[Id]))
AND
--no product reviews helpfulness
(NOT EXISTS(SELECT 1 FROM [ProductReviewHelpfulness] prh inner join [Customer] on prh.[CustomerId]=c.[Id]))
AND
--no poll voting
(NOT EXISTS(SELECT 1 FROM [PollVotingRecord] pvr inner join [Customer] on pvr.[CustomerId]=c.[Id]))
AND
--no forum topics
(NOT EXISTS(SELECT 1 FROM [Forums_Topic] ft inner join [Customer] on ft.[CustomerId]=c.[Id]))
AND
--no forum posts
(NOT EXISTS(SELECT 1 FROM [Forums_Post] fp inner join [Customer] on fp.[CustomerId]=c.[Id]))
AND
--no system accounts
(c.IsSystemAccount = 0)
--delete guests
DELETE [Customer]
WHERE [Id] IN (SELECT [CustomerID] FROM #tmp_guests)
--delete attributes
DELETE [GenericAttribute]
WHERE ([EntityID] IN (SELECT [CustomerID] FROM #tmp_guests))
AND
([KeyGroup] = N'Customer')
--total records
SELECT @TotalRecordsDeleted = COUNT(1) FROM #tmp_guests
DROP TABLE #tmp_guests
END
GO
--new setting
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'adminareasettings.richeditorallowjavascript')
BEGIN
INSERT [Setting] ([Name], [Value], [StoreId])
VALUES (N'adminareasettings.richeditorallowjavascript', N'false', 0)
END
GO
--new setting
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'ordersettings.attachpdfinvoicetoorderpaidemail')
BEGIN
INSERT [Setting] ([Name], [Value], [StoreId])
VALUES (N'ordersettings.attachpdfinvoicetoorderpaidemail', N'false', 0)
END
GO