365 lines
28 KiB
C#
365 lines
28 KiB
C#
using Microsoft.EntityFrameworkCore.Migrations;
|
|
using System;
|
|
|
|
#nullable disable
|
|
|
|
namespace gehGassi.Persistence.Migrations
|
|
{
|
|
public partial class InitialViewsAndFunctions : Migration
|
|
{
|
|
protected override void Up(MigrationBuilder migrationBuilder)
|
|
{
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.views WHERE Name = 'vw_AuditsWithNames')) BEGIN DROP VIEW [dbo].[vw_AuditsWithNames] END {Environment.NewLine} GO");
|
|
migrationBuilder.Sql($"CREATE VIEW [dbo].[vw_AuditsWithNames] AS SELECT Audits.*, U1.FullName AS UserFullName, U1.Photo AS UserPhoto FROM Audits LEFT OUTER JOIN AspNetUsers AS U1 ON Audits.UserName = U1.UserName {Environment.NewLine} GO");
|
|
|
|
|
|
var viewName = "vw_ApplicationUsersWithNames";
|
|
var sql = @"SELECT dbo.AspNetUsers.*, dbo.Customers.Name AS CustomerName,
|
|
dbo.AppUsers.FirstName + ' ' + dbo.AppUsers.LastName AS AppUserName
|
|
FROM dbo.AspNetUsers
|
|
LEFT OUTER JOIN dbo.Customers ON dbo.AspNetUsers.CustomerId = dbo.Customers.Id
|
|
LEFT OUTER JOIN dbo.AppUsers ON dbo.AspNetUsers.AppUserId = dbo.AppUsers.Id";
|
|
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.views WHERE Name = '{viewName}')) BEGIN DROP VIEW [dbo].[{viewName}] END {Environment.NewLine} GO");
|
|
migrationBuilder.Sql($"CREATE VIEW [dbo].[{viewName}] AS {sql} {Environment.NewLine} GO");
|
|
|
|
|
|
viewName = "vw_OrdersPhysical";
|
|
sql = @"SELECT O.Id, O.UniqueId, O.OrderStatus, O.PaymentType, O.PaymentStatus, O.ShipmentType, O.ShipmentStatus, O.ShipmentDate,
|
|
(SELECT Count(Id) From OrderItems WHERE OrderItems.OrderId = O.Id AND OrderItems.ProductId <> 1 AND OrderItems.ProductType = 1) as PhysicalCount,
|
|
O.Deleted, O.PaymentDate, O.PayPalOrderId, O.KlarnaOrderId, O.KlarnaShipmentSentDate, O.KlarnaShipmentError, O.PaymentInfo
|
|
FROM orders O";
|
|
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.views WHERE Name = '{viewName}')) BEGIN DROP VIEW [dbo].[{viewName}] END {Environment.NewLine} GO");
|
|
migrationBuilder.Sql($"CREATE VIEW [dbo].[{viewName}] AS {sql} {Environment.NewLine} GO");
|
|
|
|
|
|
viewName = "tv_AdvertisementCategoryWithNames";
|
|
sql = @"CREATE FUNCTION tv_AdvertisementCategoryWithNames (@language nvarchar(2), @fallback nvarchar(2)) RETURNS TABLE AS RETURN
|
|
SELECT dbo.AdvertisementCategories.*,
|
|
Isnull(JSON_VALUE(dbo.AdvertisementCategories.NameLocalized, '$.' + @language), JSON_VALUE(dbo.AdvertisementCategories.NameLocalized, '$.' + @fallback)) AS Name,
|
|
Isnull(JSON_VALUE(dbo.AdvertisementCategories.DescriptionLocalized, '$.' + @language), JSON_VALUE(dbo.AdvertisementCategories.DescriptionLocalized, '$.' + @fallback)) AS Description,
|
|
JSON_VALUE(dbo.AdvertisementCategories.ImageLanguageLocalized, '$.' + @language) AS ImageLanguage
|
|
FROM dbo.AdvertisementCategories";
|
|
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.objects WHERE Name = '{viewName}')) BEGIN DROP FUNCTION [dbo].[{viewName}] END {Environment.NewLine} GO");
|
|
migrationBuilder.Sql($"{sql} {Environment.NewLine} GO");
|
|
|
|
|
|
viewName = "tv_BranchesWithNames";
|
|
sql = @"CREATE FUNCTION tv_BranchesWithNames (@language nvarchar(2), @fallback nvarchar(2)) RETURNS TABLE AS RETURN
|
|
SELECT dbo.Branches.*,
|
|
Isnull(JSON_VALUE(dbo.Branches.NameLocalized, '$.' + @language), JSON_VALUE(dbo.Branches.NameLocalized, '$.' + @fallback)) AS Name,
|
|
Isnull(JSON_VALUE(dbo.Branches.DescriptionLocalized, '$.' + @language), JSON_VALUE(dbo.Branches.DescriptionLocalized, '$.' + @fallback)) AS Description,
|
|
JSON_VALUE(dbo.Branches.ImageLanguageLocalized, '$.' + @language) AS ImageLanguage
|
|
FROM dbo.Branches";
|
|
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.objects WHERE Name = '{viewName}')) BEGIN DROP FUNCTION [dbo].[{viewName}] END {Environment.NewLine} GO");
|
|
migrationBuilder.Sql($"{sql} {Environment.NewLine} GO");
|
|
|
|
|
|
viewName = "tv_CustomersWithNames";
|
|
sql = @"CREATE FUNCTION tv_CustomersWithNames (@language nvarchar(2), @fallback nvarchar(2)) RETURNS TABLE AS RETURN
|
|
SELECT dbo.Customers.*, dbo.CustomerTypes.Name AS TypeName, BN.Name AS BranchName
|
|
FROM dbo.Customers
|
|
LEFT JOIN dbo.CustomerTypes ON dbo.Customers.TypeId = dbo.CustomerTypes.Id
|
|
LEFT JOIN dbo.tv_BranchesWithNames(@language, @fallback) BN ON dbo.Customers.BranchId = BN.Id";
|
|
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.objects WHERE Name = '{viewName}')) BEGIN DROP FUNCTION [dbo].[{viewName}] END {Environment.NewLine} GO");
|
|
migrationBuilder.Sql($"{sql} {Environment.NewLine} GO");
|
|
|
|
|
|
viewName = "tv_AdvertisementsWithNames";
|
|
sql = @"CREATE FUNCTION tv_AdvertisementsWithNames (@language nvarchar(2), @fallback nvarchar(2)) RETURNS TABLE AS RETURN
|
|
SELECT dbo.Advertisements.*,
|
|
C.Name AS CustomerName,
|
|
AC.Name AS AdvertisementCategoryName,
|
|
Isnull(JSON_VALUE(dbo.Advertisements.NameLocalized, '$.' + @language), JSON_VALUE(dbo.Advertisements.NameLocalized, '$.' + @fallback)) AS Name,
|
|
Isnull(JSON_VALUE(dbo.Advertisements.DescriptionLocalized, '$.' + @language), JSON_VALUE(dbo.Advertisements.DescriptionLocalized, '$.' + @fallback)) AS Description,
|
|
JSON_VALUE(dbo.Advertisements.ImageLanguageLocalized, '$.' + @language) AS ImageLanguage,
|
|
JSON_VALUE(dbo.Advertisements.UrlLanguageLocalized, '$.' + @language) AS UrlLanguage
|
|
FROM dbo.Advertisements
|
|
LEFT JOIN dbo.tv_CustomersWithNames(@language, @fallback) C ON dbo.Advertisements.CustomerId = C.Id
|
|
LEFT JOIN dbo.tv_AdvertisementCategoryWithNames(@language, @fallback) AC ON dbo.Advertisements.AdvertisementCategoryId = AC.Id";
|
|
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.objects WHERE Name = '{viewName}')) BEGIN DROP FUNCTION [dbo].[{viewName}] END {Environment.NewLine} GO");
|
|
migrationBuilder.Sql($"{sql} {Environment.NewLine} GO");
|
|
|
|
|
|
viewName = "tv_BannersWithNames";
|
|
sql = @"CREATE FUNCTION tv_BannersWithNames (@language nvarchar(2), @fallback nvarchar(2)) RETURNS TABLE AS RETURN
|
|
SELECT dbo.Banners.*,
|
|
C.Name AS CustomerName,
|
|
Isnull(JSON_VALUE(dbo.Banners.NameLocalized, '$.' + @language), JSON_VALUE(dbo.Banners.NameLocalized, '$.' + @fallback)) AS Name,
|
|
JSON_VALUE(dbo.Banners.ImageLanguageLocalized, '$.' + @language) AS ImageLanguage,
|
|
JSON_VALUE(dbo.Banners.UrlLanguageLocalized, '$.' + @language) AS UrlLanguage
|
|
FROM dbo.Banners
|
|
LEFT JOIN dbo.tv_CustomersWithNames(@language, @fallback) C ON dbo.Banners.CustomerId = C.Id";
|
|
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.objects WHERE Name = '{viewName}')) BEGIN DROP FUNCTION [dbo].[{viewName}] END {Environment.NewLine} GO");
|
|
migrationBuilder.Sql($"{sql} {Environment.NewLine} GO");
|
|
|
|
|
|
viewName = "tv_ProductCategoriesWithNames";
|
|
sql = @"CREATE FUNCTION tv_ProductCategoriesWithNames (@language nvarchar(2), @fallback nvarchar(2)) RETURNS TABLE AS RETURN
|
|
SELECT dbo.ProductCategories.*,
|
|
Isnull(JSON_VALUE(dbo.ProductCategories.NameLocalized, '$.' + @language), JSON_VALUE(dbo.ProductCategories.NameLocalized, '$.' + @fallback)) AS Name,
|
|
Isnull(JSON_VALUE(dbo.ProductCategories.DescriptionLocalized, '$.' + @language), JSON_VALUE(dbo.ProductCategories.DescriptionLocalized, '$.' + @fallback)) AS Description,
|
|
JSON_VALUE(dbo.ProductCategories.ImageLanguageLocalized, '$.' + @language) AS ImageLanguage
|
|
FROM dbo.ProductCategories";
|
|
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.objects WHERE Name = '{viewName}')) BEGIN DROP FUNCTION [dbo].[{viewName}] END {Environment.NewLine} GO");
|
|
migrationBuilder.Sql($"{sql} {Environment.NewLine} GO");
|
|
|
|
|
|
viewName = "tv_TaxRatesWithNames";
|
|
sql = @"CREATE FUNCTION tv_TaxRatesWithNames (@language nvarchar(2), @fallback nvarchar(2)) RETURNS TABLE AS RETURN
|
|
SELECT dbo.TaxRates.*,
|
|
Isnull(JSON_VALUE(dbo.TaxRates.NameLocalized, '$.' + @language), JSON_VALUE(dbo.TaxRates.NameLocalized, '$.' + @fallback)) AS Name
|
|
FROM dbo.TaxRates";
|
|
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.objects WHERE Name = '{viewName}')) BEGIN DROP FUNCTION [dbo].[{viewName}] END {Environment.NewLine} GO");
|
|
migrationBuilder.Sql($"{sql} {Environment.NewLine} GO");
|
|
|
|
|
|
viewName = "tv_ProductsWithNames";
|
|
sql = @"CREATE FUNCTION tv_ProductsWithNames (@language nvarchar(2), @fallback nvarchar(2)) RETURNS TABLE AS RETURN
|
|
SELECT dbo.Products.*,
|
|
PC.Name AS ProductCategoryName,
|
|
TR.Name AS TaxRateName,
|
|
AC.Name AS AdvertisementCategoryName,
|
|
BR.Name AS BranchName,
|
|
Isnull(JSON_VALUE(dbo.Products.NameLocalized, '$.' + @language), JSON_VALUE(dbo.Products.NameLocalized, '$.' + @fallback)) AS Name,
|
|
Isnull(JSON_VALUE(dbo.Products.ShortDescriptionLocalized, '$.' + @language), JSON_VALUE(dbo.Products.ShortDescriptionLocalized, '$.' + @fallback)) AS ShortDescription,
|
|
Isnull(JSON_VALUE(dbo.Products.DescriptionLocalized, '$.' + @language), JSON_VALUE(dbo.Products.DescriptionLocalized, '$.' + @fallback)) AS Description,
|
|
JSON_VALUE(dbo.Products.ImageLanguageLocalized, '$.' + @language) AS ImageLanguage,
|
|
(SELECT Count(Id) FROM dbo.ProductCountries WHERE dbo.ProductCountries.ProductId = dbo.Products.Id) AS CountrySettingsCount
|
|
FROM dbo.Products
|
|
LEFT JOIN dbo.tv_ProductCategoriesWithNames(@language, @fallback) PC ON dbo.Products.ProductCategoryId = PC.Id
|
|
LEFT JOIN dbo.tv_TaxRatesWithNames(@language, @fallback) TR ON dbo.Products.TaxRateId = TR.Id
|
|
LEFT JOIN dbo.tv_AdvertisementCategoryWithNames(@language, @fallback) AC ON dbo.Products.AdvertisementCategoryId = AC.Id
|
|
LEFT JOIN dbo.tv_BranchesWithNames(@language, @fallback) BR ON dbo.Products.BranchId = BR.Id";
|
|
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.objects WHERE Name = '{viewName}')) BEGIN DROP FUNCTION [dbo].[{viewName}] END {Environment.NewLine} GO");
|
|
migrationBuilder.Sql($"{sql} {Environment.NewLine} GO");
|
|
|
|
|
|
viewName = "tv_CartItemsWithNames";
|
|
sql = @"CREATE FUNCTION tv_CartItemsWithNames (@language nvarchar(2), @fallback nvarchar(2)) RETURNS TABLE AS RETURN
|
|
SELECT dbo.CartItems.*,
|
|
PR.Name AS ProductName, PR.Sku AS ProductSku, IsNull(PR.ImageLanguage, PR.Image) AS ProductImage,
|
|
PR.OrderMinimumQuantity, PR.OrderMaximumQuantity
|
|
FROM dbo.CartItems
|
|
LEFT JOIN dbo.tv_ProductsWithNames(@language, @fallback) PR ON dbo.CartItems.ProductId = PR.Id";
|
|
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.objects WHERE Name = '{viewName}')) BEGIN DROP FUNCTION [dbo].[{viewName}] END {Environment.NewLine} GO");
|
|
migrationBuilder.Sql($"{sql} {Environment.NewLine} GO");
|
|
|
|
|
|
viewName = "tv_CartsWithNames";
|
|
sql = @"CREATE FUNCTION tv_CartsWithNames (@language nvarchar(2), @fallback nvarchar(2)) RETURNS TABLE AS RETURN
|
|
SELECT dbo.Carts.*,
|
|
CONCAT(APP.FirstName, ' ', APP.LastName) AS AppUserName,
|
|
C.Name AS CustomerName,
|
|
IsNull((Select SUM(Quantity) FROM dbo.CartItems WHERE dbo.CartItems.CartId = dbo.Carts.Id),0) AS ItemCount
|
|
FROM dbo.Carts
|
|
LEFT JOIN dbo.AppUsers APP ON dbo.Carts.AppUserId = APP.Id
|
|
LEFT JOIN dbo.Customers C ON dbo.Carts.CustomerId = C.Id";
|
|
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.objects WHERE Name = '{viewName}')) BEGIN DROP FUNCTION [dbo].[{viewName}] END {Environment.NewLine} GO");
|
|
migrationBuilder.Sql($"{sql} {Environment.NewLine} GO");
|
|
|
|
|
|
viewName = "tv_DogRacesWithNames";
|
|
sql = @"CREATE FUNCTION tv_DogRacesWithNames (@language nvarchar(2), @fallback nvarchar(2)) RETURNS TABLE AS RETURN
|
|
SELECT dbo.DogRaces.*,
|
|
Isnull(JSON_VALUE(dbo.DogRaces.NameLocalized, '$.' + @language), JSON_VALUE(dbo.DogRaces.NameLocalized, '$.' + @fallback)) AS Name,
|
|
Isnull(JSON_VALUE(dbo.DogRaces.DescriptionLocalized, '$.' + @language), JSON_VALUE(dbo.DogRaces.DescriptionLocalized, '$.' + @fallback)) AS Description,
|
|
Isnull(JSON_VALUE(dbo.DogRaces.AgeLocalized, '$.' + @language), JSON_VALUE(dbo.DogRaces.AgeLocalized, '$.' + @fallback)) AS Age,
|
|
Isnull(JSON_VALUE(dbo.DogRaces.SizeAndWeightLocalized, '$.' + @language), JSON_VALUE(dbo.DogRaces.SizeAndWeightLocalized, '$.' + @fallback)) AS SizeAndWeight,
|
|
Isnull(JSON_VALUE(dbo.DogRaces.PersonalityLocalized, '$.' + @language), JSON_VALUE(dbo.DogRaces.PersonalityLocalized, '$.' + @fallback)) AS Personality,
|
|
Isnull(JSON_VALUE(dbo.DogRaces.CoexistenceLocalized, '$.' + @language), JSON_VALUE(dbo.DogRaces.CoexistenceLocalized, '$.' + @fallback)) AS Coexistence,
|
|
Isnull(JSON_VALUE(dbo.DogRaces.CareLocalized, '$.' + @language), JSON_VALUE(dbo.DogRaces.CareLocalized, '$.' + @fallback)) AS Care,
|
|
Isnull(JSON_VALUE(dbo.DogRaces.DecisionLocalized, '$.' + @language), JSON_VALUE(dbo.DogRaces.DecisionLocalized, '$.' + @fallback)) AS Decision
|
|
FROM dbo.DogRaces";
|
|
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.objects WHERE Name = '{viewName}')) BEGIN DROP FUNCTION [dbo].[{viewName}] END {Environment.NewLine} GO");
|
|
migrationBuilder.Sql($"{sql} {Environment.NewLine} GO");
|
|
|
|
|
|
viewName = "tv_DogsWithNames";
|
|
sql = @"CREATE FUNCTION tv_DogsWithNames (@language nvarchar(2), @fallback nvarchar(2)) RETURNS TABLE AS RETURN
|
|
SELECT dbo.Dogs.*,
|
|
DR.Name AS DogRaceName,
|
|
CONCAT(APP.FirstName, ' ', APP.LastName) AS AppUserName
|
|
FROM dbo.Dogs
|
|
LEFT JOIN dbo.tv_DogRacesWithNames(@language, @fallback) DR ON dbo.Dogs.DogRaceId = DR.Id
|
|
LEFT JOIN dbo.AppUsers APP ON dbo.Dogs.AppUserId = APP.Id";
|
|
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.objects WHERE Name = '{viewName}')) BEGIN DROP FUNCTION [dbo].[{viewName}] END {Environment.NewLine} GO");
|
|
migrationBuilder.Sql($"{sql} {Environment.NewLine} GO");
|
|
|
|
|
|
viewName = "tv_ListingsWithNames";
|
|
sql = @"CREATE FUNCTION tv_ListingsWithNames (@language nvarchar(2), @fallback nvarchar(2)) RETURNS TABLE AS RETURN
|
|
SELECT dbo.Listings.*,
|
|
C.Name AS CustomerName,
|
|
BR.Name AS BranchName,
|
|
Isnull(JSON_VALUE(dbo.Listings.NameLocalized, '$.' + @language), JSON_VALUE(dbo.Listings.NameLocalized, '$.' + @fallback)) AS Name,
|
|
Isnull(JSON_VALUE(dbo.Listings.DescriptionLocalized, '$.' + @language), JSON_VALUE(dbo.Listings.DescriptionLocalized, '$.' + @fallback)) AS Description,
|
|
JSON_VALUE(dbo.Listings.ImageLanguageLocalized, '$.' + @language) AS ImageLanguage,
|
|
JSON_VALUE(dbo.Listings.UrlLanguageLocalized, '$.' + @language) AS UrlLanguage
|
|
FROM dbo.Listings
|
|
LEFT JOIN dbo.tv_CustomersWithNames(@language, @fallback) C ON dbo.Listings.CustomerId = C.Id
|
|
LEFT JOIN dbo.tv_BranchesWithNames(@language, @fallback) BR ON dbo.Listings.BranchId = BR.Id";
|
|
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.objects WHERE Name = '{viewName}')) BEGIN DROP FUNCTION [dbo].[{viewName}] END {Environment.NewLine} GO");
|
|
migrationBuilder.Sql($"{sql} {Environment.NewLine} GO");
|
|
|
|
|
|
viewName = "tv_NewsCategoryWithNames";
|
|
sql = @"CREATE FUNCTION tv_NewsCategoryWithNames (@language nvarchar(2), @fallback nvarchar(2)) RETURNS TABLE AS RETURN
|
|
SELECT dbo.NewsCategories.*,
|
|
Isnull(JSON_VALUE(dbo.NewsCategories.NameLocalized, '$.' + @language), JSON_VALUE(dbo.NewsCategories.NameLocalized, '$.' + @fallback)) AS Name,
|
|
Isnull(JSON_VALUE(dbo.NewsCategories.DescriptionLocalized, '$.' + @language), JSON_VALUE(dbo.NewsCategories.DescriptionLocalized, '$.' + @fallback)) AS Description,
|
|
JSON_VALUE(dbo.NewsCategories.ImageLanguageLocalized, '$.' + @language) AS ImageLanguage
|
|
FROM dbo.NewsCategories";
|
|
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.objects WHERE Name = '{viewName}')) BEGIN DROP FUNCTION [dbo].[{viewName}] END {Environment.NewLine} GO");
|
|
migrationBuilder.Sql($"{sql} {Environment.NewLine} GO");
|
|
|
|
|
|
viewName = "tv_NewsWithNames";
|
|
sql = @"CREATE FUNCTION tv_NewsWithNames (@language nvarchar(2), @fallback nvarchar(2)) RETURNS TABLE AS RETURN
|
|
SELECT dbo.News.*,
|
|
NC.Name AS CategoryName
|
|
FROM dbo.News
|
|
LEFT JOIN dbo.tv_NewsCategoryWithNames(@language, @fallback) NC ON dbo.News.CategoryId = NC.Id";
|
|
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.objects WHERE Name = '{viewName}')) BEGIN DROP FUNCTION [dbo].[{viewName}] END {Environment.NewLine} GO");
|
|
migrationBuilder.Sql($"{sql} {Environment.NewLine} GO");
|
|
|
|
|
|
viewName = "tv_NewsWithNamesDistance";
|
|
sql = @"CREATE FUNCTION tv_NewsWithNamesDistance (@language nvarchar(2), @fallback nvarchar(2), @lat float, @lng float) RETURNS TABLE AS RETURN
|
|
SELECT dbo.News.*,
|
|
NC.Name AS CategoryName
|
|
FROM dbo.News
|
|
LEFT JOIN dbo.tv_NewsCategoryWithNames(@language, @fallback) NC ON dbo.News.CategoryId = NC.Id
|
|
WHERE (Geography::Point(@lat, @lng, 4326).STDistance(Geography::Point(News.Location.Lat, News.Location.Long, 4326)) / 1000) <= Radius";
|
|
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.objects WHERE Name = '{viewName}')) BEGIN DROP FUNCTION [dbo].[{viewName}] END {Environment.NewLine} GO");
|
|
migrationBuilder.Sql($"{sql} {Environment.NewLine} GO");
|
|
|
|
|
|
viewName = "tv_PinsWithNames";
|
|
sql = @"CREATE FUNCTION tv_PinsWithNames (@language nvarchar(2), @fallback nvarchar(2)) RETURNS TABLE AS RETURN
|
|
SELECT dbo.Pins.*,
|
|
C.Name AS CustomerName,
|
|
Isnull(JSON_VALUE(dbo.Pins.NameLocalized, '$.' + @language), JSON_VALUE(dbo.Pins.NameLocalized, '$.' + @fallback)) AS Name,
|
|
Isnull(JSON_VALUE(dbo.Pins.DescriptionLocalized, '$.' + @language), JSON_VALUE(dbo.Pins.DescriptionLocalized, '$.' + @fallback)) AS Description,
|
|
JSON_VALUE(dbo.Pins.ImageLanguageLocalized, '$.' + @language) AS ImageLanguage,
|
|
JSON_VALUE(dbo.Pins.UrlLanguageLocalized, '$.' + @language) AS UrlLanguage
|
|
FROM dbo.Pins
|
|
LEFT JOIN dbo.tv_CustomersWithNames(@language, @fallback) C ON dbo.Pins.CustomerId = C.Id";
|
|
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.objects WHERE Name = '{viewName}')) BEGIN DROP FUNCTION [dbo].[{viewName}] END {Environment.NewLine} GO");
|
|
migrationBuilder.Sql($"{sql} {Environment.NewLine} GO");
|
|
|
|
|
|
viewName = "tv_ProductsWithNamesCountry";
|
|
sql = @"CREATE FUNCTION tv_ProductsWithNamesCountry (@language nvarchar(2), @fallback nvarchar(2), @country nvarchar(2)) RETURNS TABLE AS RETURN
|
|
SELECT dbo.Products.*,
|
|
PC.Name AS ProductCategoryName,
|
|
TR.Name AS TaxRateName,
|
|
AC.Name AS AdvertisementCategoryName,
|
|
BR.Name AS BranchName,
|
|
Isnull(JSON_VALUE(dbo.Products.NameLocalized, '$.' + @language), JSON_VALUE(dbo.Products.NameLocalized, '$.' + @fallback)) AS Name,
|
|
Isnull(JSON_VALUE(dbo.Products.ShortDescriptionLocalized, '$.' + @language), JSON_VALUE(dbo.Products.ShortDescriptionLocalized, '$.' + @fallback)) AS ShortDescription,
|
|
Isnull(JSON_VALUE(dbo.Products.DescriptionLocalized, '$.' + @language), JSON_VALUE(dbo.Products.DescriptionLocalized, '$.' + @fallback)) AS Description,
|
|
JSON_VALUE(dbo.Products.ImageLanguageLocalized, '$.' + @language) AS ImageLanguage,
|
|
(SELECT Count(Id) FROM dbo.ProductCountries WHERE dbo.ProductCountries.ProductId = dbo.Products.Id) AS CountrySettingsCount,
|
|
IsNull(PRC.CountryIso, @country) AS CountryIso,
|
|
IsNull(PRC.Price,0) AS PriceCountry,
|
|
IsNull(PRC.OldPrice,0) AS OldPriceCountry,
|
|
IsNull(PRC.Price2,0) AS Price2Country,
|
|
IsNull(PRC.OldPrice2,0) AS OldPrice2Country,
|
|
IsNull(PRC.NotAvailable,0) AS NotAvailable
|
|
FROM dbo.Products
|
|
LEFT JOIN dbo.tv_ProductCategoriesWithNames(@language, @fallback) PC ON dbo.Products.ProductCategoryId = PC.Id
|
|
LEFT JOIN dbo.tv_TaxRatesWithNames(@language, @fallback) TR ON dbo.Products.TaxRateId = TR.Id
|
|
LEFT JOIN dbo.tv_AdvertisementCategoryWithNames(@language, @fallback) AC ON dbo.Products.AdvertisementCategoryId = AC.Id
|
|
LEFT JOIN dbo.tv_BranchesWithNames(@language, @fallback) BR ON dbo.Products.BranchId = BR.Id
|
|
LEFT JOIN dbo.ProductCountries PRC ON dbo.Products.Id = PRC.ProductId AND PRC.CountryIso= @country";
|
|
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.objects WHERE Name = '{viewName}')) BEGIN DROP FUNCTION [dbo].[{viewName}] END {Environment.NewLine} GO");
|
|
migrationBuilder.Sql($"{sql} {Environment.NewLine} GO");
|
|
}
|
|
|
|
protected override void Down(MigrationBuilder migrationBuilder)
|
|
{
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.views WHERE Name = 'vw_AuditsWithNames')) BEGIN DROP VIEW [dbo].[vw_AuditsWithNames] END {Environment.NewLine} GO");
|
|
|
|
var viewName = "vw_ApplicationUsersWithNames";
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.views WHERE Name = '{viewName}')) BEGIN DROP VIEW [dbo].[{viewName}] END {Environment.NewLine} GO");
|
|
|
|
viewName = "vw_OrdersPhysical";
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.views WHERE Name = '{viewName}')) BEGIN DROP VIEW [dbo].[{viewName}] END {Environment.NewLine} GO");
|
|
|
|
viewName = "tv_AdvertisementCategoryWithNames";
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.objects WHERE Name = '{viewName}')) BEGIN DROP FUNCTION [dbo].[{viewName}] END {Environment.NewLine} GO");
|
|
|
|
viewName = "tv_BranchesWithNames";
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.objects WHERE Name = '{viewName}')) BEGIN DROP FUNCTION [dbo].[{viewName}] END {Environment.NewLine} GO");
|
|
|
|
viewName = "tv_CustomersWithNames";
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.objects WHERE Name = '{viewName}')) BEGIN DROP FUNCTION [dbo].[{viewName}] END {Environment.NewLine} GO");
|
|
|
|
viewName = "tv_AdvertisementsWithNames";
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.objects WHERE Name = '{viewName}')) BEGIN DROP FUNCTION [dbo].[{viewName}] END {Environment.NewLine} GO");
|
|
|
|
viewName = "tv_BannersWithNames";
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.objects WHERE Name = '{viewName}')) BEGIN DROP FUNCTION [dbo].[{viewName}] END {Environment.NewLine} GO");
|
|
|
|
viewName = "tv_ProductCategoriesWithNames";
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.objects WHERE Name = '{viewName}')) BEGIN DROP FUNCTION [dbo].[{viewName}] END {Environment.NewLine} GO");
|
|
|
|
viewName = "tv_TaxRatesWithNames";
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.objects WHERE Name = '{viewName}')) BEGIN DROP FUNCTION [dbo].[{viewName}] END {Environment.NewLine} GO");
|
|
|
|
viewName = "tv_ProductsWithNames";
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.objects WHERE Name = '{viewName}')) BEGIN DROP FUNCTION [dbo].[{viewName}] END {Environment.NewLine} GO");
|
|
|
|
viewName = "tv_CartItemsWithNames";
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.objects WHERE Name = '{viewName}')) BEGIN DROP FUNCTION [dbo].[{viewName}] END {Environment.NewLine} GO");
|
|
|
|
viewName = "tv_CartsWithNames";
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.objects WHERE Name = '{viewName}')) BEGIN DROP FUNCTION [dbo].[{viewName}] END {Environment.NewLine} GO");
|
|
|
|
viewName = "tv_DogRacesWithNames";
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.objects WHERE Name = '{viewName}')) BEGIN DROP FUNCTION [dbo].[{viewName}] END {Environment.NewLine} GO");
|
|
|
|
viewName = "tv_DogsWithNames";
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.objects WHERE Name = '{viewName}')) BEGIN DROP FUNCTION [dbo].[{viewName}] END {Environment.NewLine} GO");
|
|
|
|
viewName = "tv_ListingsWithNames";
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.objects WHERE Name = '{viewName}')) BEGIN DROP FUNCTION [dbo].[{viewName}] END {Environment.NewLine} GO");
|
|
|
|
viewName = "tv_NewsCategoryWithNames";
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.objects WHERE Name = '{viewName}')) BEGIN DROP FUNCTION [dbo].[{viewName}] END {Environment.NewLine} GO");
|
|
|
|
viewName = "tv_NewsWithNames";
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.objects WHERE Name = '{viewName}')) BEGIN DROP FUNCTION [dbo].[{viewName}] END {Environment.NewLine} GO");
|
|
|
|
viewName = "tv_NewsWithNamesDistance";
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.objects WHERE Name = '{viewName}')) BEGIN DROP FUNCTION [dbo].[{viewName}] END {Environment.NewLine} GO");
|
|
|
|
viewName = "tv_PinsWithNames";
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.objects WHERE Name = '{viewName}')) BEGIN DROP FUNCTION [dbo].[{viewName}] END {Environment.NewLine} GO");
|
|
|
|
viewName = "tv_ProductsWithNamesCountry";
|
|
migrationBuilder.Sql($"IF(exists (SELECT 1 FROM sys.objects WHERE Name = '{viewName}')) BEGIN DROP FUNCTION [dbo].[{viewName}] END {Environment.NewLine} GO");
|
|
}
|
|
}
|
|
}
|