101 lines
4.3 KiB
Transact-SQL
101 lines
4.3 KiB
Transact-SQL
CREATE PROCEDURE [BO_GetPublicKnoks]
|
|
@KnokId BIGINT = NULL,
|
|
@UserId BIGINT,
|
|
@SortOrderAsc BIT = 0,
|
|
@SortField INT = 1,
|
|
@KnoksersIFollow BIT = 0,
|
|
@Favorites BIT = 0,
|
|
@ContainsTA BIT = 0,
|
|
@ContainsFA BIT = 0,
|
|
@KnokPriceMax decimal(18, 10) = NULL,
|
|
@KnokPriceMin decimal(18, 10) = NULL,
|
|
@ExchangesIdsString NVARCHAR(1024) = NULL,
|
|
@TradingPairsString NVARCHAR(1024) = NULL,
|
|
@RatingsString NVARCHAR(1024) = NULL,
|
|
@Gaining BIT = 0,
|
|
@Losing BIT = 0,
|
|
@Published BIT = 0,
|
|
@KnokFeedExpirationHours INT = NULL,
|
|
@KnokToUSDRate decimal(22,8) = 1
|
|
AS
|
|
declare @ExchangesIds TABLE(id int NULL);
|
|
declare @Ratings TABLE(id int NULL);
|
|
declare @TradingPairs TABLE(code nvarchar(255) NULL);
|
|
|
|
insert into @ExchangesIds(id) SELECT cast(value as int)
|
|
FROM STRING_SPLIT(@ExchangesIdsString, ';')
|
|
|
|
insert into @Ratings(id) SELECT cast(value as int)
|
|
FROM STRING_SPLIT(@RatingsString, ';')
|
|
|
|
insert into @TradingPairs(code) SELECT value
|
|
FROM STRING_SPLIT(@TradingPairsString, ';')
|
|
|
|
SELECT k.KnokId, k.Currency1, k.Currency2, k.Currency, k.ExchangeId, k.UserId,
|
|
k.HasFundamental, k.HasTechnical, k.CreateDate, k.PublishedDate, k.MarketDisplayName, k.ExchangeName,
|
|
k.PotentialProfitValue, k.PotentialProfit, k.FinishTime,
|
|
k.Price, k.Price * @KnokToUSDRate AS DollarPrice, k.EntryPriceTouched,
|
|
k.HasAvatar, k.UserName, k.FirstName, k.LastName, k.[Rank],
|
|
k.UserFollowers, k.KnokserRank, k.Purchases, k.UserActiveKnoks,
|
|
(CASE WHEN EXISTS( select 1 from UserFollows e where e.UserId = @UserId and e.FollowUserId = k.UserId) THEN CAST (1 AS BIT) ELSE CAST (0 AS BIT) END) IsFollowed,
|
|
(SELECT CAST (1 AS BIT) FROM UserKnoks uk WHERE uk.UserId = @UserId AND uk.KnokId = k.KnokId) AS IsBought,
|
|
(CASE WHEN NOT @KnokFeedExpirationHours IS NULL AND DATEDIFF(MINUTE, GETUTCDATE(), DATEADD(HOUR, @KnokFeedExpirationHours, k.CreateDate)) > 0
|
|
THEN DATEDIFF(MINUTE, GETUTCDATE(), DATEADD(HOUR, @KnokFeedExpirationHours, k.CreateDate)) ELSE 0 END) AS MinutesLeftOnFeed
|
|
FROM [VW_PublicKnoks] k
|
|
|
|
-- Made for testing. Should be replaced with the joins to the actual data
|
|
-- LEFT JOIN (SELECT Signals.[dbo].[Signals] as Id, 40.0 as DollarPrice, 0.51 TokenPrice,
|
|
-- CAST(0 AS bit) EntryPriceTouched, 0.0 KnokserRatingPoints, 0 KnokserRating -- 123 as Purchases, 0 UserFollowers, 0 UserActiveKnoks
|
|
-- FROM Signals) temp on temp.Id = k.[dbo].[Signals]
|
|
|
|
WHERE k.KnokId = ISNULL(@KnokId, k.KnokId)
|
|
--and k.CreatorUserId != @UserId
|
|
--and status.IsPublished = 1
|
|
--and k.SellFinishDate >= GETUTCDATE()
|
|
AND (EXISTS(SELECT 1 FROM @ExchangesIds HAVING COUNT(1) = 0) OR EXISTS (SELECT 1 FROM @ExchangesIds e WHERE e.id = k.ExchangeId))
|
|
AND (EXISTS(SELECT 1 FROM @TradingPairs HAVING COUNT(1) = 0) OR EXISTS (SELECT 1 FROM @TradingPairs e WHERE e.code = k.Currency2))
|
|
AND ((@ContainsTA = 1 AND k.HasTechnical = 1) OR ISNULL(@ContainsTA, 0) = 0)
|
|
-- -- AND ISNULL(@ContainsTA, k.HasTechnical) = k.HasTechnical
|
|
AND (EXISTS(SELECT 1 FROM @Ratings HAVING COUNT(1) = 0) OR EXISTS (SELECT 1 FROM @Ratings t WHERE t.id = k.KnokserRank))
|
|
|
|
AND ((@ContainsFA = 1 AND k.HasFundamental = 1) OR ISNULL(@ContainsFA, 0) = 0)
|
|
AND (ISNULL(@KnoksersIFollow,0) = 0 OR EXISTS( select 1 from UserFollows e where e.UserId = @UserId and e.FollowUserId = k.UserId))
|
|
AND ISNULL(@KnokPriceMax, k.Price)>= k.Price AND ISNULL(@KnokPriceMin, k.Price)<= k.Price
|
|
AND (ISNULL(@Favorites, 0) = 0 or exists(SELECT 1 FROM [dbo].[UserFavoriteKnoks] fav WHERE fav.KnokId = k.KnokId and fav.UserId = @UserId))
|
|
-- AND u.UserId <> @UserId Customer should have posibility to determine own order
|
|
ORDER BY
|
|
CASE @SortOrderAsc WHEN 1 THEN
|
|
CASE WHEN ISNULL(@SortField, 1) = 1 THEN
|
|
CASE @SortField
|
|
WHEN NULL THEN k.SellFinishDate -- TimeLeft
|
|
WHEN 1 THEN k.SellFinishDate -- TimeLeft
|
|
END
|
|
END
|
|
END ASC,
|
|
|
|
CASE @SortOrderAsc WHEN 0 THEN
|
|
CASE WHEN ISNULL(@SortField, 1) = 1 THEN
|
|
CASE @SortField
|
|
WHEN NULL THEN k.SellFinishDate -- TimeLeft
|
|
WHEN 1 THEN k.SellFinishDate -- TimeLeft
|
|
END
|
|
END
|
|
END DESC,
|
|
CASE @SortOrderAsc WHEN 1 THEN
|
|
CASE WHEN ISNULL(@SortField, 1) > 1 THEN
|
|
CASE @SortField
|
|
WHEN 2 THEN k.Price -- Price
|
|
WHEN 3 THEN k.UserKnoks -- TimeLeft
|
|
END
|
|
END
|
|
END ASC,
|
|
|
|
CASE @SortOrderAsc WHEN 0 THEN
|
|
CASE WHEN ISNULL(@SortField, 1) > 1 THEN
|
|
CASE @SortField
|
|
WHEN 2 THEN k.Price -- Price
|
|
WHEN 3 THEN k.UserKnoks -- TimeLeft
|
|
END
|
|
END
|
|
END DESC
|
|
RETURN 0 |