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 '
--product variant name
SET @sql = @sql + '
UNION
SELECT pv.ProductId
FROM ProductVariant pv with (NOLOCK)
WHERE '
IF @UseFullTextSearch = 1
SET @sql = @sql + 'CONTAINS(pv.[Name], @Keywords) '
ELSE
SET @sql = @sql + 'PATINDEX(@Keywords, pv.[Name]) > 0 '
--SKU
SET @sql = @sql + '
UNION
SELECT pv.ProductId
FROM ProductVariant pv with (NOLOCK)
WHERE '
IF @UseFullTextSearch = 1
SET @sql = @sql + 'CONTAINS(pv.[Sku], @Keywords) '
ELSE
SET @sql = @sql + 'PATINDEX(@Keywords, pv.[Sku]) > 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 '
--product short description
IF @SearchDescriptions = 1
BEGIN
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 '
--product variant description
SET @sql = @sql + '
UNION
SELECT pv.ProductId
FROM ProductVariant pv with (NOLOCK)
WHERE '
IF @UseFullTextSearch = 1
SET @sql = @sql + 'CONTAINS(pv.[Description], @Keywords) '
ELSE
SET @sql = @sql + 'PATINDEX(@Keywords, pv.[Description]) > 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
--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)
--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
IF @ShowHidden = 0
OR @PriceMin > 0
OR @PriceMax > 0
OR @OrderBy = 10 /* Price: Low to High */
OR @OrderBy = 11 /* Price: High to Low */
BEGIN
SET @sql = @sql + '
LEFT JOIN ProductVariant pv with (NOLOCK)
ON p.Id = pv.ProductId'
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 product tag
IF ISNULL(@ProductTagId, 0) != 0
BEGIN
SET @sql = @sql + '
AND pptm.ProductTag_Id = ' + CAST(@ProductTagId AS nvarchar(max))
END
IF @ShowHidden = 0
BEGIN
SET @sql = @sql + '
AND p.Published = 1
AND pv.Published = 1
AND pv.Deleted = 0
AND (getutcdate() BETWEEN ISNULL(pv.AvailableStartDateTimeUtc, ''1/1/1900'') and ISNULL(pv.AvailableEndDateTimeUtc, ''1/1/2999''))'
END
--min price
IF @PriceMin > 0
BEGIN
SET @sql = @sql + '
AND (
(
--special price (specified price and valid date range)
(pv.SpecialPrice IS NOT NULL AND (getutcdate() BETWEEN isnull(pv.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(pv.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
AND
(pv.SpecialPrice >= ' + CAST(@PriceMin AS nvarchar(max)) + ')
)
OR
(
--regular price (price isnt specified or date range isnt valid)
(pv.SpecialPrice IS NULL OR (getutcdate() NOT BETWEEN isnull(pv.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(pv.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
AND
(pv.Price >= ' + CAST(@PriceMin AS nvarchar(max)) + ')
)
)'
END
--max price
IF @PriceMax > 0
BEGIN
SET @sql = @sql + '
AND (
(
--special price (specified price and valid date range)
(pv.SpecialPrice IS NOT NULL AND (getutcdate() BETWEEN isnull(pv.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(pv.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
AND
(pv.SpecialPrice <= ' + CAST(@PriceMax AS nvarchar(max)) + ')
)
OR
(
--regular price (price isnt specified or date range isnt valid)
(pv.SpecialPrice IS NULL OR (getutcdate() NOT BETWEEN isnull(pv.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(pv.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
AND
(pv.Price <= ' + CAST(@PriceMax 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
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 = ' pv.[Price] ASC'
ELSE IF @OrderBy = 11 /* Price: High to Low */
SET @sql_orderby = ' pv.[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
--PRINT (@sql)
EXEC sp_executesql @sql
DROP TABLE #FilteredCategoryIds
DROP TABLE #FilteredSpecs
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]
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(1000))
FROM #FilterableSpecs
DROP TABLE #FilterableSpecs
END
--return products
SELECT TOP (@RowsToReturn)
p.*
FROM
#PageIndex [pi]
INNER JOIN Product p on p.Id = [pi].[ProductId]
WHERE
[pi].IndexId > @PageLowerBound AND
[pi].IndexId < @PageUpperBound
ORDER BY
[pi].IndexId
DROP TABLE #PageIndex
END
GO
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[nop_getprimarykey_indexname]') AND [type] in (N'FN', N'IF', N'TF'))
DROP FUNCTION [nop_getprimarykey_indexname]
GO
CREATE FUNCTION [dbo].[nop_getprimarykey_indexname]
(
@table_name nvarchar(1000) = null
)
RETURNS nvarchar(1000)
AS
BEGIN
DECLARE @index_name nvarchar(1000)
SELECT @index_name = i.name
FROM sys.tables AS tbl
INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id)
WHERE (i.is_unique=1 and i.is_disabled=0) and (tbl.name=@table_name)
RETURN @index_name
END
GO
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[FullText_IsSupported]') AND OBJECTPROPERTY(object_id,N'IsProcedure') = 1)
DROP PROCEDURE [FullText_IsSupported]
GO
CREATE PROCEDURE [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')
END
GO
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[FullText_Enable]') AND OBJECTPROPERTY(object_id,N'IsProcedure') = 1)
DROP PROCEDURE [FullText_Enable]
GO
CREATE PROCEDURE [FullText_Enable]
AS
BEGIN
--create catalog
EXEC('
IF NOT EXISTS (SELECT 1 FROM sys.fulltext_catalogs WHERE [name] = ''nopCommerceFullTextCatalog'')
CREATE FULLTEXT CATALOG [nopCommerceFullTextCatalog] AS DEFAULT')
--create indexes
DECLARE @create_index_text nvarchar(4000)
SET @create_index_text = '
IF NOT EXISTS (SELECT 1 FROM sys.fulltext_indexes WHERE object_id = object_id(''[Product]''))
CREATE FULLTEXT INDEX ON [Product]([Name], [ShortDescription], [FullDescription])
KEY INDEX [' + dbo.[nop_getprimarykey_indexname] ('Product') + '] ON [nopCommerceFullTextCatalog] WITH CHANGE_TRACKING AUTO'
EXEC(@create_index_text)
SET @create_index_text = '
IF NOT EXISTS (SELECT 1 FROM sys.fulltext_indexes WHERE object_id = object_id(''[ProductVariant]''))
CREATE FULLTEXT INDEX ON [ProductVariant]([Name], [Description], [SKU])
KEY INDEX [' + dbo.[nop_getprimarykey_indexname] ('ProductVariant') + '] ON [nopCommerceFullTextCatalog] WITH CHANGE_TRACKING AUTO'
EXEC(@create_index_text)
SET @create_index_text = '
IF NOT EXISTS (SELECT 1 FROM sys.fulltext_indexes WHERE object_id = object_id(''[LocalizedProperty]''))
CREATE FULLTEXT INDEX ON [LocalizedProperty]([LocaleValue])
KEY INDEX [' + dbo.[nop_getprimarykey_indexname] ('LocalizedProperty') + '] ON [nopCommerceFullTextCatalog] WITH CHANGE_TRACKING AUTO'
EXEC(@create_index_text)
END
GO
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[FullText_Disable]') AND OBJECTPROPERTY(object_id,N'IsProcedure') = 1)
DROP PROCEDURE [FullText_Disable]
GO
CREATE PROCEDURE [FullText_Disable]
AS
BEGIN
EXEC('
--drop indexes
IF EXISTS (SELECT 1 FROM sys.fulltext_indexes WHERE object_id = object_id(''[Product]''))
DROP FULLTEXT INDEX ON [Product]
')
EXEC('
IF EXISTS (SELECT 1 FROM sys.fulltext_indexes WHERE object_id = object_id(''[ProductVariant]''))
DROP FULLTEXT INDEX ON [ProductVariant]
')
EXEC('
IF EXISTS (SELECT 1 FROM sys.fulltext_indexes WHERE object_id = object_id(''[LocalizedProperty]''))
DROP FULLTEXT INDEX ON [LocalizedProperty]
')
--drop catalog
EXEC('
IF EXISTS (SELECT 1 FROM sys.fulltext_catalogs WHERE [name] = ''nopCommerceFullTextCatalog'')
DROP FULLTEXT CATALOG [nopCommerceFullTextCatalog]
')
END
GO
--language pack import
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[LanguagePackImport]') AND OBJECTPROPERTY(object_id,N'IsProcedure') = 1)
DROP PROCEDURE [LanguagePackImport]
GO
CREATE PROCEDURE [LanguagePackImport]
(
@LanguageId int,
@XmlPackage xml
)
AS
BEGIN
IF EXISTS(SELECT * FROM [Language] WHERE [Id] = @LanguageId)
BEGIN
CREATE TABLE #LocaleStringResourceTmp
(
[LanguageId] [int] NOT NULL,
[ResourceName] [nvarchar](200) NOT NULL,
[ResourceValue] [nvarchar](MAX) NOT NULL
)
INSERT INTO #LocaleStringResourceTmp (LanguageID, ResourceName, ResourceValue)
SELECT @LanguageId, nref.value('@Name', 'nvarchar(200)'), nref.value('Value[1]', 'nvarchar(MAX)')
FROM @XmlPackage.nodes('//Language/LocaleResource') AS R(nref)
DECLARE @ResourceName nvarchar(200)
DECLARE @ResourceValue nvarchar(MAX)
DECLARE cur_localeresource CURSOR FOR
SELECT LanguageID, ResourceName, ResourceValue
FROM #LocaleStringResourceTmp
OPEN cur_localeresource
FETCH NEXT FROM cur_localeresource INTO @LanguageId, @ResourceName, @ResourceValue
WHILE @@FETCH_STATUS = 0
BEGIN
IF (EXISTS (SELECT 1 FROM [LocaleStringResource] WHERE LanguageID=@LanguageId AND ResourceName=@ResourceName))
BEGIN
UPDATE [LocaleStringResource]
SET [ResourceValue]=@ResourceValue
WHERE LanguageID=@LanguageId AND ResourceName=@ResourceName
END
ELSE
BEGIN
INSERT INTO [LocaleStringResource]
(
[LanguageId],
[ResourceName],
[ResourceValue]
)
VALUES
(
@LanguageId,
@ResourceName,
@ResourceValue
)
END
FETCH NEXT FROM cur_localeresource INTO @LanguageId, @ResourceName, @ResourceValue
END
CLOSE cur_localeresource
DEALLOCATE cur_localeresource
DROP TABLE #LocaleStringResourceTmp
END
END
GO
--Add 'Weight' column to [Shipment] and [OrderProductVariant] tables
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[Shipment]') and NAME='TotalWeight')
BEGIN
ALTER TABLE [Shipment]
ADD [TotalWeight] decimal(18, 4) NULL
END
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[OrderProductVariant]') and NAME='ItemWeight')
BEGIN
ALTER TABLE [OrderProductVariant]
ADD [ItemWeight] decimal(18, 4) NULL
END
GO
--delete obsolete permission
DELETE FROM [PermissionRecord]
WHERE [SystemName] = N'ManagePromotionFeeds'
GO
DELETE FROM [Setting]
WHERE [Name] = N'smssettings.activesmsprovidersystemnames'
GO
DELETE FROM [PermissionRecord]
WHERE [SystemName] = N'ManageSMSProviders'
GO
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'clickatellsettings.enabled')
BEGIN
INSERT [Setting] ([Name], [Value])
VALUES (N'clickatellsettings.enabled', N'false')
END
GO
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'verizonsettings.enabled')
BEGIN
INSERT [Setting] ([Name], [Value])
VALUES (N'verizonsettings.enabled', N'false')
END
GO
--Add 'AllowGuestsToVote' column to [Poll] table
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[Poll]') and NAME='AllowGuestsToVote')
BEGIN
ALTER TABLE [Poll]
ADD [AllowGuestsToVote] bit NULL
END
GO
UPDATE [Poll]
SET [AllowGuestsToVote] = 0
WHERE [AllowGuestsToVote] IS NULL
GO
ALTER TABLE [Poll] ALTER COLUMN [AllowGuestsToVote] bit NOT NULL
GO
--'Recurring payment cancelled' message template
IF NOT EXISTS (
SELECT 1
FROM [MessageTemplate]
WHERE [Name] = N'RecurringPaymentCancelled.StoreOwnerNotification')
BEGIN
INSERT [MessageTemplate] ([Name], [BccEmailAddresses], [Subject], [Body], [IsActive], [EmailAccountId])
VALUES (N'RecurringPaymentCancelled.StoreOwnerNotification', null, N'%Store.Name%. Recurring payment cancelled', N'%Store.Name%
%Customer.FullName% (%Customer.Email%) has just cancelled a recurring payment ID=%RecurringPayment.ID%.
', 1, 0)
END
GO
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'catalogsettings.manufacturersblockitemstodisplay')
BEGIN
INSERT [Setting] ([Name], [Value])
VALUES (N'catalogsettings.manufacturersblockitemstodisplay', N'5')
END
GO
IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[Order]') and NAME='OrderWeight')
BEGIN
ALTER TABLE [Order]
DROP COLUMN [OrderWeight]
END
GO
--Add 'AllowedQuantities' column to [ProductVariant] table
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[ProductVariant]') and NAME='AllowedQuantities')
BEGIN
ALTER TABLE [ProductVariant]
ADD [AllowedQuantities] nvarchar(1000) NULL
END
GO
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'mediasettings.autocompletesearchthumbpicturesize')
BEGIN
INSERT [Setting] ([Name], [Value])
VALUES (N'mediasettings.autocompletesearchthumbpicturesize', N'20')
END
GO
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'catalogsettings.showproductimagesinsearchautocomplete')
BEGIN
INSERT [Setting] ([Name], [Value])
VALUES (N'catalogsettings.showproductimagesinsearchautocomplete', N'false')
END
GO
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'frooglesettings.passshippinginfo')
BEGIN
INSERT [Setting] ([Name], [Value])
VALUES (N'frooglesettings.passshippinginfo', N'false')
END
GO
IF (EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[ShippingByWeight]') and OBJECTPROPERTY(object_id, N'IsUserTable') = 1))
AND (NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[ShippingByWeight]') and NAME='StateProvinceId'))
BEGIN
EXEC ('ALTER TABLE [ShippingByWeight]
ADD [StateProvinceId] int NULL')
END
GO
IF (EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[ShippingByWeight]') and OBJECTPROPERTY(object_id, N'IsUserTable') = 1))
AND (EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[ShippingByWeight]') and NAME='StateProvinceId'))
BEGIN
EXEC ('UPDATE [ShippingByWeight]
SET [StateProvinceId] = 0
WHERE [StateProvinceId] IS NULL')
END
GO
IF (EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[ShippingByWeight]') and OBJECTPROPERTY(object_id, N'IsUserTable') = 1))
AND (EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[ShippingByWeight]') and NAME='StateProvinceId'))
BEGIN
EXEC('ALTER TABLE [ShippingByWeight] ALTER COLUMN [StateProvinceId] int NOT NULL')
END
GO
IF (EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[ShippingByWeight]') and OBJECTPROPERTY(object_id, N'IsUserTable') = 1))
AND (NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[ShippingByWeight]') and NAME='Zip'))
BEGIN
EXEC ('ALTER TABLE [ShippingByWeight]
ADD [Zip] nvarchar(400) NULL')
END
GO
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'catalogsettings.fileuploadallowedextensions')
BEGIN
INSERT [Setting] ([Name], [Value])
VALUES (N'catalogsettings.fileuploadallowedextensions', N'')
END
GO
--new activity types
IF NOT EXISTS (SELECT 1 FROM [ActivityLogType] WHERE [SystemKeyword] = N'PublicStore.ViewCategory')
BEGIN
INSERT [ActivityLogType] ([SystemKeyword], [Name], [Enabled])
VALUES (N'PublicStore.ViewCategory', N'Public store. View a category', N'false')
END
GO
IF NOT EXISTS (SELECT 1 FROM [ActivityLogType] WHERE [SystemKeyword] = N'PublicStore.ViewManufacturer')
BEGIN
INSERT [ActivityLogType] ([SystemKeyword], [Name], [Enabled])
VALUES (N'PublicStore.ViewManufacturer', N'Public store. View a manufacturer', N'false')
END
GO
IF NOT EXISTS (SELECT 1 FROM [ActivityLogType] WHERE [SystemKeyword] = N'PublicStore.ViewProduct')
BEGIN
INSERT [ActivityLogType] ([SystemKeyword], [Name], [Enabled])
VALUES (N'PublicStore.ViewProduct', N'Public store. View a product', N'false')
END
GO
IF NOT EXISTS (SELECT 1 FROM [ActivityLogType] WHERE [SystemKeyword] = N'PublicStore.PlaceOrder')
BEGIN
INSERT [ActivityLogType] ([SystemKeyword], [Name], [Enabled])
VALUES (N'PublicStore.PlaceOrder', N'Public store. Place an order', N'false')
END
GO