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 + '
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 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 (
(
--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 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
--update message templates
UPDATE [MessageTemplate] SET [Body] = REPLACE([Body], 'ReturnRequest.ID', 'ReturnRequest.CustomNumber')
GO
--new setting
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'ordersettings.returnrequestnumbermask')
BEGIN
INSERT [Setting] ([Name], [Value], [StoreId])
VALUES (N'ordersettings.returnrequestnumbermask', N'{ID}', 0)
END
GO
--new column
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[ReturnRequest]') and NAME='CustomNumber')
BEGIN
ALTER TABLE [ReturnRequest]
ADD [CustomNumber] NVARCHAR(MAX) NULL
END
GO
UPDATE [ReturnRequest] SET [CustomNumber] = CAST([Id] AS NVARCHAR(200)) WHERE [CustomNumber] IS NULL OR [CustomNumber] = N''
GO
ALTER TABLE [ReturnRequest] ALTER COLUMN [CustomNumber] NVARCHAR(MAX) NOT NULL
GO
--new setting
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'vendorsettings.allowvendorstoeditinfo')
BEGIN
INSERT [Setting] ([Name], [Value], [StoreId])
VALUES (N'vendorsettings.allowvendorstoeditinfo', N'False', 0)
END
GO
--new setting
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'vendorsettings.notifystoreowneraboutvendorinformationchange')
BEGIN
INSERT [Setting] ([Name], [Value], [StoreId])
VALUES (N'vendorsettings.notifystoreowneraboutvendorinformationchange', N'True', 0)
END
GO
-- new message template
IF NOT EXISTS (SELECT 1 FROM [dbo].[MessageTemplate] WHERE [Name] = N'VendorInformationChange.StoreOwnerNotification')
BEGIN
INSERT [dbo].[MessageTemplate] ([Name], [BccEmailAddresses], [Subject], [Body], [IsActive], [AttachedDownloadId], [EmailAccountId], [LimitedToStores], [DelayPeriodId])
VALUES (N'VendorInformationChange.StoreOwnerNotification', NULL, N'%Store.Name%. Vendor information change.', N'%Store.Name%
Vendor %Vendor.Name% (%Vendor.Email%) has just changed information about itself.
', 1, 0, 0, 0, 0)
END
GO
--new setting
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'ordersettings.GeneratePdfInvoiceInCustomerLanguage')
BEGIN
INSERT [Setting] ([Name], [Value], [StoreId])
VALUES (N'ordersettings.GeneratePdfInvoiceInCustomerLanguage', N'true', 0)
END
GO
--new column
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[Campaign]') and NAME='CustomerRoleId')
BEGIN
ALTER TABLE [Campaign]
ADD [CustomerRoleId] INT NULL
END
GO
UPDATE [Campaign]
SET [CustomerRoleId] = 0
WHERE [CustomerRoleId] IS NULL
GO
ALTER TABLE [Campaign] ALTER COLUMN [CustomerRoleId] INT NOT NULL
GO
GO
--new column
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[Discount]') and NAME='IsCumulative')
BEGIN
ALTER TABLE [Discount]
ADD [IsCumulative] bit NULL
END
GO
UPDATE [Discount]
SET [IsCumulative] = 0
WHERE [IsCumulative] IS NULL
GO
ALTER TABLE [Discount] ALTER COLUMN [IsCumulative] bit NOT NULL
GO
--new setting
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'shippingsettings.shiptosameaddress')
BEGIN
INSERT [Setting] ([Name], [Value], [StoreId])
VALUES (N'shippingsettings.shiptosameaddress', N'False', 0)
END
GO
--new setting
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'seosettings.customheadtags')
BEGIN
INSERT [Setting] ([Name], [Value], [StoreId])
VALUES (N'seosettings.customheadtags', N'', 0)
END
GO
--new activity types
IF NOT EXISTS (SELECT 1 FROM [ActivityLogType] WHERE [SystemKeyword] = N'EditActivityLogTypes')
BEGIN
INSERT [ActivityLogType] ([SystemKeyword], [Name], [Enabled])
VALUES (N'EditActivityLogTypes', N'Edit activity log types', N'true')
END
GO
--new activity types
IF NOT EXISTS (SELECT 1 FROM [ActivityLogType] WHERE [SystemKeyword] = N'DeleteActivityLog')
BEGIN
INSERT [ActivityLogType] ([SystemKeyword], [Name], [Enabled])
VALUES (N'DeleteActivityLog', N'Delete activity log', N'true')
END
GO
--new setting
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'storeinformationsettings.logopictureid')
BEGIN
INSERT [Setting] ([Name], [Value], [StoreId])
VALUES (N'storeinformationsettings.logopictureid', N'0', 0)
END
GO
--delete setting
DELETE FROM [Setting]
WHERE [name] = N'shippingsettings.pickupinstorefee'
GO
--new setting
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'shippingsettings.displaypickuppointsonmap')
BEGIN
INSERT [Setting] ([Name], [Value], [StoreId])
VALUES (N'shippingsettings.displaypickuppointsonmap', N'false', 0)
END
GO
--new setting
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'shippingsettings.googlemapsapikey')
BEGIN
INSERT [Setting] ([Name], [Value], [StoreId])
VALUES (N'shippingsettings.googlemapsapikey', N'', 0)
END
GO
--new setting
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'shippingsettings.activepickuppointprovidersystemnames')
BEGIN
INSERT [Setting] ([Name], [Value], [StoreId])
VALUES (N'shippingsettings.activepickuppointprovidersystemnames', N'', 0)
END
GO
--new column
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[Order]') and NAME='PickupAddressId')
BEGIN
ALTER TABLE [Order]
ADD [PickupAddressId] int NULL
END
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE name = 'Order_PickupAddress' AND parent_object_id = Object_id('Order') AND Objectproperty(object_id, N'IsForeignKey') = 1)
BEGIN
ALTER TABLE [dbo].[Order]
DROP CONSTRAINT [Order_PickupAddress]
END
GO
ALTER TABLE [dbo].[Order] WITH CHECK ADD CONSTRAINT [Order_PickupAddress] FOREIGN KEY([PickupAddressId])
REFERENCES [dbo].[Address] ([Id])
GO
--new setting
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'adminareasettings.hideadvertisementsonadminarea')
BEGIN
INSERT [Setting] ([Name], [Value], [StoreId])
VALUES (N'adminareasettings.hideadvertisementsonadminarea', N'false', 0)
END
GO
--delete setting
DELETE FROM [Setting]
WHERE [name] = N'commonsettings.hideadvertisementsonadminarea'
GO
--new setting
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'customersettings.deleteguesttaskolderthanminutes')
BEGIN
INSERT [Setting] ([Name], [Value], [StoreId])
VALUES (N'customersettings.deleteguesttaskolderthanminutes', N'1440', 0)
END
GO
--delete setting
DELETE FROM [Setting]
WHERE [name] = N'commonsettings.deleteguesttaskolderthanminutes'
GO
--delete permission
DELETE FROM [PermissionRecord]
WHERE [SystemName] = N'ManageMeasures'
GO
--new setting
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'ordersettings.autoupdateordertotalsoneditingorder')
BEGIN
INSERT [Setting] ([Name], [Value], [StoreId])
VALUES (N'ordersettings.autoupdateordertotalsoneditingorder', N'false', 0)
END
GO
--new setting
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'producteditorsettings.weight')
BEGIN
INSERT [Setting] ([Name], [Value], [StoreId])
VALUES (N'producteditorsettings.weight', N'true', 0)
END
GO
--new setting
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'producteditorsettings.dimensions')
BEGIN
INSERT [Setting] ([Name], [Value], [StoreId])
VALUES (N'producteditorsettings.dimensions', N'true', 0)
END
GO
--new setting
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'catalogsettings.exportimportproductattributes')
BEGIN
INSERT [Setting] ([Name], [Value], [StoreId])
VALUES (N'catalogsettings.exportimportproductattributes', N'True', 0)
END
GO
--new setting
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'producteditorsettings.productattributes')
BEGIN
INSERT [Setting] ([Name], [Value], [StoreId])
VALUES (N'producteditorsettings.productattributes', N'true', 0)
END
GO
--new setting
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'producteditorsettings.specificationattributes')
BEGIN
INSERT [Setting] ([Name], [Value], [StoreId])
VALUES (N'producteditorsettings.specificationattributes', N'true', 0)
END
GO