--upgrade scripts from nopCommerce 2.40 to nopCommerce 2.50
--new locale resources
declare @resources xml
--a resource will be delete if its value is empty
set @resources='
Pack by dimensions
Pack by one item per package
Pack by volume
Packing type
Choose preferred packing type.
Package volume
Enter your package volume.
Created from
The creation from date for the search.
Created to
The creation to date for the search.
Approve selected
Disapprove selected
Export to XML (all)
Export to XML (selected)
Export to Excel (all)
Export to Excel (selected)
Excel file
Xml file
NOTE: It can take up to several minutes.
NOTE: DO NOT click twice.
CSV file
You are going to lose any unsaved changes. Are you sure?
Specified store URL matches this store URL
Specified store URL ({0}) doesn''t match this store URL ({1})
Primary exchange rate currency is set
Primary exchange rate currency. The rate should be set to 1.
Primary exchange rate currency is not set
Primary store currency is set
Primary store currency is not set
Default weight is set
Default weight. The ratio should be set to 1.
Default weight is not set
Default dimension is set
Default dimension. The ratio should be set to 1.
Default dimension is not set
Only one offline shipping rate computation method is recommended to use
Payment methods are OK
You don''t have active payment methods
The primary exchange rate currency can''t be deleted.
The primary store currency can''t be deleted.
The primary weight can''t be deleted.
The primary dimension can''t be deleted.
The state can''t be deleted. It has associated addresses.
Manual plugin installation
Upload the plugin to the /plugins folder in your nopCommerce directory.
Restart your application (or click ''Reload list of plugins'' button).
Scroll down through the list of plugins to find the newly installed plugin.
Click on the ''Install'' link to install the plugin.
Note: If you''re running nopCommerce in medium trust, then it''s recommended to clear your \Plugins\bin\ directory
Editing
This grid allows the bulk editing of the ''Friendly name'' and ''Display order'' fields. To enter edit mode just click a cell.
Installation
Show on login page
Check to show CAPTCHA on login page.
Show on ''email wishlist to a friend'' page
Check to show CAPTCHA on ''email wishlist to a friend'' page.
Show on ''email product to a friend'' page
Check to show CAPTCHA on ''email product to a friend'' page.
Name: A to Z
Name: Z to A
Price: Low to High
Price: High to Low
Display wishlist after adding product
If checked, a customer will be taken to the Wishlist page immediately after adding a product to their wishlist. If unchecked, a customer will stay on the same page that they are adding the product to the wishlist from.
The product has been added to the wishlist
Display shipment events
Check if you want your customers to see shipment events on their shipment details pages (if supported by your shipping rate computation method).
Shipment status events
Event
Location
Country
Date
Departed
Export scanned
Origin scanned
Arrived
Not delivered
Booked
Delivered
Add product
Click on interested product variant
Do not to forget to update order totals after adding this product.
Name
SKU
Price (incl tax)
Enter product price (incl tax).
Price (excl tax)
Enter product price (excl tax).
Quantity
Enter quantity
Total (incl tax)
Enter total (incl tax).
Total (excl tax)
Enter total (excl tax).
Return request(s)
Gift cards(s)
Products never purchased
Run report
Start date
The start date for the search.
End date
The end date for the search.
Name
Summary
Tax
Total
Profit
Download
Automatically search sub categories
Tracking code with {ECOMMERCE} line
Paste the tracking code generated by Google Analytics here. {GOOGLEID} and {ECOMMERCE} will be dynamically replaced.
Tracking code for {ECOMMERCE} part, with {DETAILS} line
Paste the tracking code generated by Google analytics here. {ORDERID}, {SITE}, {TOTAL}, {TAX}, {SHIP}, {CITY}, {STATEPROVINCE}, {COUNTRY}, {DETAILS} will be dynamically replaced.
Tracking code for {DETAILS} part
Paste the tracking code generated by Google analytics here. {ORDERID}, {PRODUCTSKU}, {PRODUCTNAME}, {CATEGORYNAME}, {UNITPRICE}, {QUANTITY} will be dynamically replaced.
Please upload a file
Please select an answer
Include products from subcategories
Check if you want a category details page to include products from subcategories.
Automatically generate a file
Check if you want a file to be automatically generated.
A task period (minutes)
Specify a task period in minutes (generation of a new Froogle file).
If a task settings (''Automatically generate a file'') have been changed, please restart the application
Generated file path (static)
A file path of the generated Froogle file. It''s static for your store and can be shared with the Froogle service.
The comma-separated list of product variant identifiers (e.g. 77, 123, 156). You can find a product variant ID on its details page. You can also specify the comma-separated list of product variant identifiers with quantities ({Product variant ID}:{Quantity}. for example, 77:1, 123:2, 156:3). And you can also specify the comma-separated list of product variant identifiers with quantity range ({Product variant ID}:{Min quantity}-{Max quantity}. for example, 77:1-3, 123:2-5, 156:3-8).
The comma-separated list of product variant identifiers (e.g. 77, 123, 156). You can find a product variant ID on its details page. You can also specify the comma-separated list of product variant identifiers with quantities ({Product variant ID}:{Quantity}. for example, 77:1, 123:2, 156:3). And you can also specify the comma-separated list of product variant identifiers with quantity range ({Product variant ID}:{Min quantity}-{Max quantity}. for example, 77:1-3, 123:2-5, 156:3-8).
File upload
Maximum file size is {0} KB
Add a new product to order #{0}
Add product ''{0}'' to order #{1}
back to product list
back to order details
Company
Search by company.
Phone
Search by a phone number.
Zip code
Search by zip code.
Live currency rates
Dropoff Type
Choose preferred dropoff type.
Business service center
Drop box
Regular pickup
Request courier
Station
Maximum avatar size is {0} bytes
Shipping options could not be loaded
Shipments
Shipment #
Tracking number
Date shipped
Date delivered
Not yet
View shipment details
back to order details
Set a tracking number of the current shipment.
Set tracking number
The date this shipment was shipped.
The date this shipment was delivered.
Set as delivered
Products shipped
Product
Qty ordered
Qty shipped
Qty to ship
Add shipment
Add a new shipment to order #{0}
The new shipment has been added successfully.
No products selected
The shipment has been deleted successfully.
Partially shipped
Shipments
Shipment #
Tracking number
Date shipped
Date delivered
Not yet
View details
Shipment details
Shipment #{0}
Order #
Shipping Method
Shipping Address
Email
Phone
Fax
Shipped product(s)
SKU
Name
Qty shipped
Print packaging slip
Shipment #{0}
Order #{0}
Available for pre-order
Check if this item is available for Pre-Order. It also displays "Pre-order" button instead of "Add to cart".
Pre-order
Manage inventory
Stock qty
Please wait several seconds before placing a new order (already placed another order several seconds ago).
'
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
--Customer currency rate issue fix
ALTER TABLE [dbo].[Order] ALTER COLUMN [CurrencyRate] decimal(18, 8) NOT NULL
GO
--new setting
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'catalogsettings.displaytierpriceswithdiscounts')
BEGIN
INSERT [Setting] ([Name], [Value])
VALUES (N'catalogsettings.displaytierpriceswithdiscounts', N'true')
END
GO
--new setting
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'fedexsettings.packingpackagevolume')
BEGIN
INSERT [Setting] ([Name], [Value])
VALUES (N'fedexsettings.packingpackagevolume', N'5184')
END
GO
--Update stored procedure according to the sort options
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[ProductLoadAllPaged]') AND OBJECTPROPERTY(object_id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[ProductLoadAllPaged]
GO
CREATE PROCEDURE [dbo].[ProductLoadAllPaged]
(
@CategoryId int = 0,
@ManufacturerId int = 0,
@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(MAX) = null,
@SearchDescriptions bit = 0,
@FilteredSpecs nvarchar(300) = null, --filter by attributes (comma-separated list). 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
@PageIndex int = 0,
@PageSize int = 2147483644,
@ShowHidden bit = 0,
@TotalRecords int = null OUTPUT
)
AS
BEGIN
--init
DECLARE @SearchKeywords bit
SET @SearchKeywords = 1
IF (@Keywords IS NULL OR @Keywords = N'')
SET @SearchKeywords = 0
SET @Keywords = isnull(@Keywords, '')
SET @Keywords = '%' + rtrim(ltrim(@Keywords)) + '%'
--filter by attributes
SET @FilteredSpecs = isnull(@FilteredSpecs, '')
CREATE TABLE #FilteredSpecs
(
SpecificationAttributeOptionId int not null
)
INSERT INTO #FilteredSpecs (SpecificationAttributeOptionId)
SELECT CAST(data as int) FROM dbo.[nop_splitstring_to_table](@FilteredSpecs, ',');
DECLARE @SpecAttributesCount int
SELECT @SpecAttributesCount = 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
)
INSERT INTO #DisplayOrderTmp ([ProductId])
SELECT p.Id
FROM Product p with (NOLOCK)
LEFT OUTER JOIN Product_Category_Mapping pcm with (NOLOCK) ON p.Id=pcm.ProductId
LEFT OUTER JOIN Product_Manufacturer_Mapping pmm with (NOLOCK) ON p.Id=pmm.ProductId
LEFT OUTER JOIN Product_ProductTag_Mapping pptm with (NOLOCK) ON p.Id=pptm.Product_Id
LEFT OUTER JOIN ProductVariant pv with (NOLOCK) ON p.Id = pv.ProductId
--searching of the localized values
--comment the line below if you don't use it. It'll improve the performance
LEFT OUTER JOIN LocalizedProperty lp with (NOLOCK) ON p.Id = lp.EntityId AND lp.LanguageId = @LanguageId AND lp.LocaleKeyGroup = N'Product'
WHERE
(
(
@CategoryId IS NULL OR @CategoryId=0
OR (pcm.CategoryId=@CategoryId AND (@FeaturedProducts IS NULL OR pcm.IsFeaturedProduct=@FeaturedProducts))
)
AND (
@ManufacturerId IS NULL OR @ManufacturerId=0
OR (pmm.ManufacturerId=@ManufacturerId AND (@FeaturedProducts IS NULL OR pmm.IsFeaturedProduct=@FeaturedProducts))
)
AND (
@ProductTagId IS NULL OR @ProductTagId=0
OR pptm.ProductTag_Id=@ProductTagId
)
AND (
@ShowHidden = 1 OR p.Published = 1
)
AND
(
p.Deleted=0
)
AND
(
@ShowHidden = 1 OR pv.Published = 1
)
AND
(
@ShowHidden = 1 OR pv.Deleted = 0
)
AND (
--min price
(@PriceMin IS NULL OR @PriceMin=0)
OR
(
--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 >= @PriceMin)
)
OR
(
--regular price (price isn't specified or date range isn't 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 >= @PriceMin)
)
)
AND (
--max price
(@PriceMax IS NULL OR @PriceMax=2147483644) -- max value
OR
(
--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 <= @PriceMax)
)
OR
(
--regular price (price isn't specified or date range isn't 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 <= @PriceMax)
)
)
AND (
@SearchKeywords = 0 or
(
-- search standard content
patindex(@Keywords, p.name) > 0
or patindex(@Keywords, pv.name) > 0
or patindex(@Keywords, pv.sku) > 0
or (@SearchDescriptions = 1 and patindex(@Keywords, p.ShortDescription) > 0)
or (@SearchDescriptions = 1 and patindex(@Keywords, p.FullDescription) > 0)
or (@SearchDescriptions = 1 and patindex(@Keywords, pv.Description) > 0)
--searching of the localized values
--comment the lines below if you don't use it. It'll improve the performance
or (lp.LocaleKey = N'Name' and patindex(@Keywords, lp.LocaleValue) > 0)
or (@SearchDescriptions = 1 and lp.LocaleKey = N'ShortDescription' and patindex(@Keywords, lp.LocaleValue) > 0)
or (@SearchDescriptions = 1 and lp.LocaleKey = N'FullDescription' and patindex(@Keywords, lp.LocaleValue) > 0)
)
)
AND
(
@ShowHidden = 1
OR
(getutcdate() between isnull(pv.AvailableStartDateTimeUtc, '1/1/1900') and isnull(pv.AvailableEndDateTimeUtc, '1/1/2999'))
)
AND
(
--filter by specs
@SpecAttributesCount = 0
OR
(
NOT EXISTS(
SELECT 1
FROM #FilteredSpecs [fs]
WHERE [fs].SpecificationAttributeOptionId NOT IN (
SELECT psam.SpecificationAttributeOptionId
FROM dbo.Product_SpecificationAttribute_Mapping psam
WHERE psam.AllowFiltering = 1 AND psam.ProductId = p.Id
)
)
)
)
)
ORDER BY
--category position
CASE WHEN @OrderBy = 0 AND @CategoryId IS NOT NULL AND @CategoryId > 0
THEN pcm.DisplayOrder END ASC,
--manufacturer position
CASE WHEN @OrderBy = 0 AND @ManufacturerId IS NOT NULL AND @ManufacturerId > 0
THEN pmm.DisplayOrder END ASC,
--sort by name (there's no any position if category or manufactur is not specified)
CASE WHEN @OrderBy = 0
THEN p.[Name] END ASC,
--Name: A to Z
CASE WHEN @OrderBy = 5
THEN p.[Name] END ASC, --THEN dbo.[nop_getnotnullnotempty](pl.[Name],p.[Name]) END ASC,
--Name: Z to A
CASE WHEN @OrderBy = 6
THEN p.[Name] END DESC, --THEN dbo.[nop_getnotnullnotempty](pl.[Name],p.[Name]) END DESC,
--Price: Low to High
CASE WHEN @OrderBy = 10
THEN pv.Price END ASC,
--Price: High to Low
CASE WHEN @OrderBy = 11
THEN pv.Price END DESC,
--Created on
CASE WHEN @OrderBy = 15
THEN p.CreatedOnUtc END DESC
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 with (NOLOCK)
GROUP BY ProductId
ORDER BY min([Id])
--total records
SET @TotalRecords = @@rowcount
SET ROWCOUNT @RowsToReturn
DROP TABLE #DisplayOrderTmp
--return products (returned properties should be synchronized with 'Product' entity)
SELECT
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
IndexId
SET ROWCOUNT 0
DROP TABLE #PageIndex
END
GO
--new setting
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'shoppingcartsettings.displaywishlistafteraddingproduct')
BEGIN
INSERT [Setting] ([Name], [Value])
VALUES (N'shoppingcartsettings.displaywishlistafteraddingproduct', N'true')
END
GO
--more SQL indexes
IF NOT EXISTS (SELECT 1 from sys.indexes WHERE [NAME]=N'IX_Product_Deleted_and_Published' and object_id=object_id(N'[dbo].[Product]'))
BEGIN
CREATE NONCLUSTERED INDEX [IX_Product_Deleted_and_Published] ON [dbo].[Product]
(
[Published] ASC,
[Deleted] ASC
)
END
GO
IF NOT EXISTS (SELECT 1 from sys.indexes WHERE [NAME]=N'IX_Product_Published' and object_id=object_id(N'[dbo].[Product]'))
BEGIN
CREATE NONCLUSTERED INDEX [IX_Product_Published] ON [dbo].[Product]
(
[Published] ASC
)
END
GO
IF NOT EXISTS (SELECT 1 from sys.indexes WHERE [NAME]=N'IX_Product_ShowOnHomepage' and object_id=object_id(N'[dbo].[Product]'))
BEGIN
CREATE NONCLUSTERED INDEX [IX_Product_ShowOnHomepage] ON [dbo].[Product]
(
[ShowOnHomepage] ASC
)
END
GO
IF NOT EXISTS (SELECT 1 from sys.indexes WHERE [NAME]=N'IX_ProductVariant_ProductId_2' and object_id=object_id(N'[dbo].[ProductVariant]'))
BEGIN
CREATE NONCLUSTERED INDEX [IX_ProductVariant_ProductId_2] ON [dbo].[ProductVariant]
(
[ProductId] ASC
)
END
GO
IF NOT EXISTS (SELECT 1 from sys.indexes WHERE [NAME]=N'IX_PCM_Product_and_Category' and object_id=object_id(N'[dbo].[Product_Category_Mapping]'))
BEGIN
CREATE NONCLUSTERED INDEX [IX_PCM_Product_and_Category] ON [dbo].[Product_Category_Mapping]
(
[CategoryId] ASC,
[ProductId] ASC
)
END
GO
IF NOT EXISTS (SELECT 1 from sys.indexes WHERE [NAME]=N'IX_PMM_Product_and_Manufacturer' and object_id=object_id(N'[dbo].[Product_Manufacturer_Mapping]'))
BEGIN
CREATE NONCLUSTERED INDEX [IX_PMM_Product_and_Manufacturer] ON [dbo].[Product_Manufacturer_Mapping]
(
[ManufacturerId] ASC,
[ProductId] ASC
)
END
GO
--New fast [ProductLoadAllPaged] stored procedure
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[ProductLoadAllPaged]') AND OBJECTPROPERTY(object_id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[ProductLoadAllPaged]
GO
CREATE PROCEDURE [dbo].[ProductLoadAllPaged]
(
@CategoryId int = 0,
@ManufacturerId int = 0,
@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(MAX) = null,
@SearchDescriptions bit = 0,
@FilteredSpecs nvarchar(300) = null, --filter by attributes (comma-separated list). 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
@PageIndex int = 0,
@PageSize int = 2147483644,
@ShowHidden bit = 0,
@TotalRecords int = null OUTPUT
)
AS
BEGIN
/* Products that filtered by keywords */
CREATE TABLE #KeywordProducts
(
[ProductId] int NOT NULL
)
DECLARE
@SearchKeywords bit,
@sql nvarchar(max),
@sql_orderby nvarchar(max)
SET NOCOUNT ON
--filter by keywords
IF ISNULL(@Keywords, '') != ''
BEGIN
SET @SearchKeywords = 1
SET @Keywords = isnull(@Keywords, '')
SET @Keywords = '%' + rtrim(ltrim(@Keywords)) + '%'
SET @sql = '
INSERT INTO #KeywordProducts ([ProductId])
SELECT p.Id
FROM Product p with (NOLOCK)
WHERE PATINDEX(@Keywords, p.name) > 0
UNION
SELECT pv.ProductId
FROM ProductVariant pv with (NOLOCK)
WHERE PATINDEX(@Keywords, pv.name) > 0
UNION
SELECT pv.ProductId
FROM ProductVariant pv with (NOLOCK)
WHERE PATINDEX(@Keywords, pv.sku) > 0
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''
AND PATINDEX(@Keywords, lp.LocaleValue) > 0'
IF @SearchDescriptions = 1 SET @sql = @sql + '
UNION
SELECT p.Id
FROM Product p with (NOLOCK)
WHERE PATINDEX(@Keywords, p.ShortDescription) > 0
UNION
SELECT p.Id
FROM Product p with (NOLOCK)
WHERE PATINDEX(@Keywords, p.FullDescription) > 0
UNION
SELECT pv.ProductId
FROM ProductVariant pv with (NOLOCK)
WHERE PATINDEX(@Keywords, pv.Description) > 0
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''
AND PATINDEX(@Keywords, lp.LocaleValue) > 0
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''
AND PATINDEX(@Keywords, lp.LocaleValue) > 0'
--PRINT (@sql)
EXEC sp_executesql @sql, N'@Keywords nvarchar(MAX)', @Keywords
END
ELSE
BEGIN
SET @SearchKeywords = 0
END
--filter by attributes
SET @FilteredSpecs = isnull(@FilteredSpecs, '')
CREATE TABLE #FilteredSpecs
(
SpecificationAttributeOptionId int not null
)
INSERT INTO #FilteredSpecs (SpecificationAttributeOptionId)
SELECT CAST(data as int) FROM dbo.[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 @CategoryId > 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 @CategoryId > 0
BEGIN
SET @sql = @sql + '
AND pcm.CategoryId = ' + CAST(@CategoryId AS nvarchar(max))
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 dbo.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 @CategoryId > 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 #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
--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
--new shipping setting
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'shippingsettings.displaytrackingurltocustomers')
BEGIN
INSERT [Setting] ([Name], [Value])
VALUES (N'shippingsettings.displaytrackingurltocustomers', N'false')
END
GO
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'shippingsettings.displayshipmenteventstocustomers')
BEGIN
INSERT [Setting] ([Name], [Value])
VALUES (N'shippingsettings.displayshipmenteventstocustomers', N'false')
END
GO
--'New order note' message template
IF NOT EXISTS (
SELECT 1
FROM [dbo].[MessageTemplate]
WHERE [Name] = N'Customer.NewOrderNote')
BEGIN
INSERT [dbo].[MessageTemplate] ([Name], [BccEmailAddresses], [Subject], [Body], [IsActive], [EmailAccountId])
VALUES (N'Customer.NewOrderNote', null, N'%Store.Name%. New order note has been added', N'%Store.Name%
Hello %Customer.FullName%,
New order note has been added to your account:
"%Order.NewNoteText%".
%Order.OrderURLForCustomer%
', 1, 0)
END
GO
--New [ProductLoadAllPaged] stored procedure (allow searching in several categories)
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[ProductLoadAllPaged]') AND OBJECTPROPERTY(object_id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[ProductLoadAllPaged]
GO
CREATE PROCEDURE [dbo].[ProductLoadAllPaged]
(
@CategoryIds nvarchar(300) = null, --a list of category IDs (comma-separated list). e.g. 1,2,3
@ManufacturerId int = 0,
@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(MAX) = null,
@SearchDescriptions bit = 0,
@FilteredSpecs nvarchar(300) = null, --filter by attributes (comma-separated list). 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
@PageIndex int = 0,
@PageSize int = 2147483644,
@ShowHidden bit = 0,
@TotalRecords int = null OUTPUT
)
AS
BEGIN
/* Products that filtered by keywords */
CREATE TABLE #KeywordProducts
(
[ProductId] int NOT NULL
)
DECLARE
@SearchKeywords bit,
@sql nvarchar(max),
@sql_orderby nvarchar(max)
SET NOCOUNT ON
--filter by keywords
IF ISNULL(@Keywords, '') != ''
BEGIN
SET @SearchKeywords = 1
SET @Keywords = isnull(@Keywords, '')
SET @Keywords = '%' + rtrim(ltrim(@Keywords)) + '%'
SET @sql = '
INSERT INTO #KeywordProducts ([ProductId])
SELECT p.Id
FROM Product p with (NOLOCK)
WHERE PATINDEX(@Keywords, p.name) > 0
UNION
SELECT pv.ProductId
FROM ProductVariant pv with (NOLOCK)
WHERE PATINDEX(@Keywords, pv.name) > 0
UNION
SELECT pv.ProductId
FROM ProductVariant pv with (NOLOCK)
WHERE PATINDEX(@Keywords, pv.sku) > 0
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''
AND PATINDEX(@Keywords, lp.LocaleValue) > 0'
IF @SearchDescriptions = 1 SET @sql = @sql + '
UNION
SELECT p.Id
FROM Product p with (NOLOCK)
WHERE PATINDEX(@Keywords, p.ShortDescription) > 0
UNION
SELECT p.Id
FROM Product p with (NOLOCK)
WHERE PATINDEX(@Keywords, p.FullDescription) > 0
UNION
SELECT pv.ProductId
FROM ProductVariant pv with (NOLOCK)
WHERE PATINDEX(@Keywords, pv.Description) > 0
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''
AND PATINDEX(@Keywords, lp.LocaleValue) > 0
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''
AND PATINDEX(@Keywords, lp.LocaleValue) > 0'
--PRINT (@sql)
EXEC sp_executesql @sql, N'@Keywords nvarchar(MAX)', @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 dbo.[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 dbo.[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 dbo.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
--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
--new Google Analytics setting
DELETE FROM [Setting]
WHERE [Name] = N'googleanalyticssettings.javascript'
GO
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'googleanalyticssettings.trackingscript')
BEGIN
INSERT [Setting] ([Name], [Value])
VALUES (N'googleanalyticssettings.trackingscript', N'
')
END
GO
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'googleanalyticssettings.ecommercescript')
BEGIN
INSERT [Setting] ([Name], [Value])
VALUES (N'googleanalyticssettings.ecommercescript', N'_gaq.push([''_addTrans'', ''{ORDERID}'', ''{SITE}'', ''{TOTAL}'', ''{TAX}'', ''{SHIP}'', ''{CITY}'', ''{STATEPROVINCE}'', ''{COUNTRY}'']);
{DETAILS}
_gaq.push([''_trackTrans'']); ')
END
GO
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'googleanalyticssettings.ecommercedetailscript')
BEGIN
INSERT [Setting] ([Name], [Value])
VALUES (N'googleanalyticssettings.ecommercedetailscript', N'_gaq.push([''_addItem'', ''{ORDERID}'', ''{PRODUCTSKU}'', ''{PRODUCTNAME}'', ''{CATEGORYNAME}'', ''{UNITPRICE}'', ''{QUANTITY}'' ]); ')
END
GO
--new setting
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'catalogsettings.showproductsfromsubcategories')
BEGIN
INSERT [Setting] ([Name], [Value])
VALUES (N'catalogsettings.showproductsfromsubcategories', N'false')
END
GO
--new setting
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'frooglesettings.staticfilename')
BEGIN
INSERT [Setting] ([Name], [Value])
VALUES (N'frooglesettings.staticfilename', N'froogle_' + CAST(CAST(RAND() * 1000000000 AS INT) AS NVARCHAR) + N'.xml')
END
GO
--'Froogle static file generation' schedule task (disabled by default)
IF NOT EXISTS (
SELECT 1
FROM [dbo].[ScheduleTask]
WHERE [Name] = N'Froogle static file generation')
BEGIN
INSERT [dbo].[ScheduleTask] ([Name], [Seconds], [Type], [Enabled], [StopOnError])
VALUES (N'Froogle static file generation', 3600, N'Nop.Plugin.Feed.Froogle.StaticFileGenerationTask, Nop.Plugin.Feed.Froogle', 0, 0)
END
GO
--more SQL indexes
IF NOT EXISTS (SELECT 1 from sys.indexes WHERE [NAME]=N'IX_PSAM_AllowFiltering' and object_id=object_id(N'[dbo].[Product_SpecificationAttribute_Mapping]'))
BEGIN
CREATE NONCLUSTERED INDEX [IX_PSAM_AllowFiltering] ON [dbo].[Product_SpecificationAttribute_Mapping]
(
[AllowFiltering] ASC
)
INCLUDE ([ProductId],[SpecificationAttributeOptionId])
END
GO
IF NOT EXISTS (SELECT 1 from sys.indexes WHERE [NAME]=N'IX_PSAM_SpecificationAttributeOptionId_AllowFiltering' and object_id=object_id(N'[dbo].[Product_SpecificationAttribute_Mapping]'))
BEGIN
CREATE NONCLUSTERED INDEX [IX_PSAM_SpecificationAttributeOptionId_AllowFiltering] ON [dbo].[Product_SpecificationAttribute_Mapping]
(
[SpecificationAttributeOptionId] ASC,
[AllowFiltering] ASC
)
INCLUDE ([ProductId])
END
GO
--Add 'Guid' column to [Download] table
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[dbo].[Download]') and NAME='DownloadGuid')
BEGIN
ALTER TABLE [dbo].[Download]
ADD [DownloadGuid] uniqueidentifier NULL
END
GO
UPDATE [dbo].[Download]
SET [DownloadGuid] = NEWID()
WHERE [DownloadGuid] IS NULL
GO
ALTER TABLE [dbo].[Download] ALTER COLUMN [DownloadGuid] uniqueidentifier NOT NULL
GO
--new setting
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'catalogsettings.fileuploadmaximumsizebytes')
BEGIN
INSERT [Setting] ([Name], [Value])
VALUES (N'catalogsettings.fileuploadmaximumsizebytes', N'204800')
END
GO
--delete old
DELETE FROM [Setting]
WHERE [Name] = N'catalogsettings.ensurewehavefilterablespecattributes'
GO
--Update stored procedure according to the new search parameters (return filterable specs)
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[ProductLoadAllPaged]') AND OBJECTPROPERTY(object_id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[ProductLoadAllPaged]
GO
CREATE PROCEDURE [dbo].[ProductLoadAllPaged]
(
@CategoryIds nvarchar(300) = null, --a list of category IDs (comma-separated list). e.g. 1,2,3
@ManufacturerId int = 0,
@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(MAX) = null,
@SearchDescriptions bit = 0,
@FilteredSpecs nvarchar(300) = null, --filter by attributes (comma-separated list). 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
@PageIndex int = 0,
@PageSize int = 2147483644,
@ShowHidden bit = 0,
@LoadFilterableSpecificationAttributeOptionIds bit = 0, --a value indicating whether we should load the specification attribute option identifiers applied to loaded products (all pages)
@FilterableSpecificationAttributeOptionIds nvarchar(100) = 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,
@sql nvarchar(max),
@sql_orderby nvarchar(max)
SET NOCOUNT ON
--filter by keywords
IF ISNULL(@Keywords, '') != ''
BEGIN
SET @SearchKeywords = 1
SET @Keywords = isnull(@Keywords, '')
SET @Keywords = '%' + rtrim(ltrim(@Keywords)) + '%'
SET @sql = '
INSERT INTO #KeywordProducts ([ProductId])
SELECT p.Id
FROM Product p with (NOLOCK)
WHERE PATINDEX(@Keywords, p.name) > 0
UNION
SELECT pv.ProductId
FROM ProductVariant pv with (NOLOCK)
WHERE PATINDEX(@Keywords, pv.name) > 0
UNION
SELECT pv.ProductId
FROM ProductVariant pv with (NOLOCK)
WHERE PATINDEX(@Keywords, pv.sku) > 0
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''
AND PATINDEX(@Keywords, lp.LocaleValue) > 0'
IF @SearchDescriptions = 1 SET @sql = @sql + '
UNION
SELECT p.Id
FROM Product p with (NOLOCK)
WHERE PATINDEX(@Keywords, p.ShortDescription) > 0
UNION
SELECT p.Id
FROM Product p with (NOLOCK)
WHERE PATINDEX(@Keywords, p.FullDescription) > 0
UNION
SELECT pv.ProductId
FROM ProductVariant pv with (NOLOCK)
WHERE PATINDEX(@Keywords, pv.Description) > 0
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''
AND PATINDEX(@Keywords, lp.LocaleValue) > 0
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''
AND PATINDEX(@Keywords, lp.LocaleValue) > 0'
--PRINT (@sql)
EXEC sp_executesql @sql, N'@Keywords nvarchar(MAX)', @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 dbo.[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 dbo.[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 dbo.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
--Add new columns to [ScheduleTask] table
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[dbo].[ScheduleTask]') and NAME='LastStartUtc')
BEGIN
ALTER TABLE [dbo].[ScheduleTask]
ADD [LastStartUtc] datetime NULL
END
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[dbo].[ScheduleTask]') and NAME='LastEndUtc')
BEGIN
ALTER TABLE [dbo].[ScheduleTask]
ADD [LastEndUtc] datetime NULL
END
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[dbo].[ScheduleTask]') and NAME='LastSuccessUtc')
BEGIN
ALTER TABLE [dbo].[ScheduleTask]
ADD [LastSuccessUtc] datetime NULL
END
GO
--new 'FedEx' plugin setting
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'fedexsettings.dropofftype')
BEGIN
INSERT [Setting] ([Name], [Value])
VALUES (N'fedexsettings.dropofftype', N'0')
END
GO
--new setting
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'shippingsettings.returnvalidoptionsifthereareany')
BEGIN
INSERT [Setting] ([Name], [Value])
VALUES (N'shippingsettings.returnvalidoptionsifthereareany', N'true')
END
GO
--new shipment functionality
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Shipment]') and OBJECTPROPERTY(object_id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Shipment](
[Id] [int] IDENTITY(1,1) NOT NULL,
[OrderId] int NOT NULL,
[TrackingNumber] [nvarchar](max) NULL,
[ShippedDateUtc] [datetime] NOT NULL,
[DeliveryDateUtc] [datetime] 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 = 'Shipment_Order'
AND parent_object_id = Object_id('Shipment')
AND Objectproperty(object_id,N'IsForeignKey') = 1)
ALTER TABLE dbo.Shipment
DROP CONSTRAINT Shipment_Order
GO
ALTER TABLE [dbo].[Shipment] WITH CHECK ADD CONSTRAINT [Shipment_Order] FOREIGN KEY([OrderId])
REFERENCES [dbo].[Order] ([Id])
ON DELETE CASCADE
GO
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Shipment_OrderProductVariant]') and OBJECTPROPERTY(object_id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Shipment_OrderProductVariant](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ShipmentId] int NOT NULL,
[OrderProductVariantId] int NOT NULL,
[Quantity] 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 = 'ShipmentOrderProductVariant_Shipment'
AND parent_object_id = Object_id('Shipment_OrderProductVariant')
AND Objectproperty(object_id,N'IsForeignKey') = 1)
ALTER TABLE dbo.Shipment_OrderProductVariant
DROP CONSTRAINT ShipmentOrderProductVariant_Shipment
GO
ALTER TABLE [dbo].[Shipment_OrderProductVariant] WITH CHECK ADD CONSTRAINT [ShipmentOrderProductVariant_Shipment] FOREIGN KEY([ShipmentId])
REFERENCES [dbo].[Shipment] ([Id])
ON DELETE CASCADE
GO
--new message template
IF NOT EXISTS (
SELECT 1
FROM [dbo].[MessageTemplate]
WHERE [Name] = N'ShipmentSent.CustomerNotification')
BEGIN
INSERT [dbo].[MessageTemplate] ([Name], [BccEmailAddresses], [Subject], [Body], [IsActive], [EmailAccountId])
VALUES (N'ShipmentSent.CustomerNotification', null, N'Your order from %Store.Name% has been shipped.', N' %Store.Name%
Hello %Order.CustomerFullName%!,
Good news! You order has been shipped.
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%
Shipped Products:
%Shipment.Product(s)%
', 1, 0)
END
GO
IF NOT EXISTS (
SELECT 1
FROM [dbo].[MessageTemplate]
WHERE [Name] = N'ShipmentDelivered.CustomerNotification')
BEGIN
INSERT [dbo].[MessageTemplate] ([Name], [BccEmailAddresses], [Subject], [Body], [IsActive], [EmailAccountId])
VALUES (N'ShipmentDelivered.CustomerNotification', null, N'Your order from %Store.Name% has been delivered.', N' %Store.Name%
Hello %Order.CustomerFullName%,
Good news! You order has been delivered.
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%
Delivered Products:
%Shipment.Product(s)%
', 1, 0)
END
GO
--delete old shipping message templates
DELETE FROM [MessageTemplate]
WHERE [Name] = N'OrderShipped.CustomerNotification'
GO
DELETE FROM [MessageTemplate]
WHERE [Name] = N'OrderDelivered.CustomerNotification'
GO
--create shipments for the previous orders
IF (EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[dbo].[Order]') and NAME='ShippedDateUtc')
AND EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[dbo].[Order]') and NAME='DeliveryDateUtc')
AND EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[dbo].[Order]') and NAME='TrackingNumber'))
BEGIN
EXEC('
DECLARE @OrderId int
DECLARE cur_order CURSOR FOR
SELECT [Id]
FROM [Order]
ORDER BY [Id]
OPEN cur_order
FETCH NEXT FROM cur_order INTO @OrderId
WHILE @@FETCH_STATUS = 0
BEGIN
--shipping status
DECLARE @ShippingStatusId int
SET @ShippingStatusId = null -- clear cache (variable scope)
SELECT @ShippingStatusId = [ShippingStatusId] FROM [Order] WHERE [Id]=@OrderId
--is order already shipped or delivered?
IF (@ShippingStatusId = 30 OR @ShippingStatusId = 40)
BEGIN
--select shippable order product variant identifiers
CREATE TABLE #OrderedProductVariants
(
[Id] int NOT NULL,
[Quantity] int NOT NULL
)
INSERT INTO #OrderedProductVariants ([Id], [Quantity])
SELECT opv.[Id], opv.[Quantity] FROM [Order] o
JOIN [OrderProductVariant] opv ON o.[Id] = opv.[OrderId]
JOIN [ProductVariant] pv ON opv.[ProductVariantId] = pv.[Id]
WHERE o.[Id] = @OrderID AND pv.[IsShipEnabled] = 1
DECLARE @HasShippableProducts bit
SET @HasShippableProducts = null -- clear cache (variable scope)
SELECT @HasShippableProducts = COUNT(1) FROM #OrderedProductVariants
IF @HasShippableProducts = 1
BEGIN
--tracking number
DECLARE @TrackingNumber nvarchar(MAX)
SET @TrackingNumber = null -- clear cache (variable scope)
SELECT @TrackingNumber = [TrackingNumber] FROM [Order] WHERE [Id]=@OrderId
--shipped date
DECLARE @ShippedDateUtc datetime
SET @ShippedDateUtc = null -- clear cache (variable scope)
SELECT @ShippedDateUtc = [ShippedDateUtc] FROM [Order] WHERE [Id]=@OrderId
IF (@ShippedDateUtc is null)
BEGIN
SELECT @ShippedDateUtc = [CreatedOnUtc] FROM [Order] WHERE [Id]=@OrderId
END
--delivery date
DECLARE @DeliveryDateUtc datetime
SET @DeliveryDateUtc = null -- clear cache (variable scope)
SELECT @DeliveryDateUtc = [DeliveryDateUtc] FROM [Order] WHERE [Id]=@OrderId
--insert shipment
DECLARE @ShipmentId int
SET @ShipmentId = null -- clear cache (variable scope)
INSERT INTO [Shipment] ([OrderId], [TrackingNumber], [ShippedDateUtc], [DeliveryDateUtc])
VALUES (@OrderId, @TrackingNumber, @ShippedDateUtc, @DeliveryDateUtc)
SET @ShipmentId = @@IDENTITY
--now insert shipment order product variants
INSERT INTO [Shipment_OrderProductVariant] ([ShipmentId], [OrderProductVariantId], [Quantity])
SELECT @ShipmentId, [Id], [Quantity]
FROM #OrderedProductVariants
END
DROP TABLE #OrderedProductVariants
END
--fetch next identifier
FETCH NEXT FROM cur_order INTO @OrderId
END
CLOSE cur_order
DEALLOCATE cur_order
')
END
GO
--drop old column
IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[dbo].[Order]') and NAME='ShippedDateUtc')
BEGIN
ALTER TABLE [dbo].[Order] DROP COLUMN [ShippedDateUtc]
END
GO
IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[dbo].[Order]') and NAME='DeliveryDateUtc')
BEGIN
ALTER TABLE [dbo].[Order] DROP COLUMN [DeliveryDateUtc]
END
GO
IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[dbo].[Order]') and NAME='TrackingNumber')
BEGIN
ALTER TABLE [dbo].[Order] DROP COLUMN [TrackingNumber]
END
GO
--"pre-order" support
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('[dbo].[ProductVariant]') and NAME='AvailableForPreOrder')
BEGIN
ALTER TABLE [dbo].[ProductVariant]
ADD [AvailableForPreOrder] bit NULL
END
GO
UPDATE [dbo].[ProductVariant]
SET [AvailableForPreOrder] = 0
WHERE [AvailableForPreOrder] IS NULL
GO
ALTER TABLE [dbo].[ProductVariant] ALTER COLUMN [AvailableForPreOrder] bit NOT NULL
GO
--new setting
IF NOT EXISTS (SELECT 1 FROM [Setting] WHERE [name] = N'ordersettings.minimumorderplacementinterval')
BEGIN
INSERT [Setting] ([Name], [Value])
VALUES (N'ordersettings.minimumorderplacementinterval', N'30')
END
GO
--delete setting
DELETE FROM [Setting]
WHERE [Name] = N'commonsettings.enablehttpcompression'
GO