95 lines
3.3 KiB
Transact-SQL
95 lines
3.3 KiB
Transact-SQL
CREATE PROCEDURE [BO_GetEndedKnoks]
|
|
@KnokId BIGINT = NULL,
|
|
@UserId BIGINT,
|
|
@SortOrderAsc BIT = 0,
|
|
@SortField INT = 1,
|
|
@Losing BIT = 0,
|
|
@Gaining BIT = 0,
|
|
@ExchangesIdsString NVARCHAR(1024) = NULL,
|
|
@TradingPairsString NVARCHAR(1024) = NULL,
|
|
@Knokser NVARCHAR(255) = NULL,
|
|
@RangeSettings [DurationCRTAggrItemsList] readonly,
|
|
@LoseStatusString NVARCHAR(1024) = NULL
|
|
AS
|
|
|
|
declare @ExchangesIds TABLE(id int NULL);
|
|
declare @TradingPairs TABLE(code nvarchar(255) NULL);
|
|
declare @LoseStatuses TABLE(id int NULL);
|
|
|
|
insert into @ExchangesIds(id) SELECT cast(value as int)
|
|
FROM STRING_SPLIT(@ExchangesIdsString, ';')
|
|
|
|
insert into @TradingPairs(code) SELECT value
|
|
FROM STRING_SPLIT(@TradingPairsString, ';')
|
|
|
|
insert into @LoseStatuses(id) SELECT cast(value as int)
|
|
FROM STRING_SPLIT(@LoseStatusString, ';')
|
|
|
|
--Get ended knoks
|
|
SELECT Duration, CreateDate, KnokId, Currency1, Currency2, Currency, MarketDisplayName, ExchangeId, UserId,
|
|
EntryPriceFrom, EntryPriceTo, ExitPriceFrom, ExitPriceTo, StopLoss, ExchangeName,
|
|
HasAvatar, UserName, FirstName, LastName,
|
|
EndedStatus,
|
|
DistanceFromTarget,
|
|
Purchases,
|
|
CurrentProfit,
|
|
PotentialProfitValue,
|
|
CurrentPrice,
|
|
k.CloseDate,
|
|
CAST(CASE WHEN EXISTS(SELECT 1 FROM TraderFeedbacks fb where fb.KnokId = k.KnokId AND fb.UserId = @UserId AND fb.FeedbackType = 1) THEN 1 ELSE 0 END AS BIT) AS FeedbackExists,
|
|
k.Precision
|
|
|
|
FROM [dbo].[VW_EndedKnoks] k with(nolock)
|
|
WHERE ISNULL(@KnokId, k.KnokId) = k.KnokId
|
|
AND (EXISTS (SELECT 1 FROM UserKnoks uk WHERE uk.UserId = @UserId AND uk.KnokId = k.KnokId))
|
|
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 (@Knokser IS NULL OR k.LastName like '%' + @Knokser + '%' OR k.FirstName like '%' + @Knokser + '%')
|
|
AND (ISNULL(@Losing, 0) = 0 OR (@Losing = 1 AND k.EndedStatus <> 4))
|
|
AND (ISNULL(@Gaining, 0) = 0 OR (@Gaining = 1 AND k.EndedStatus = 4))
|
|
AND (EXISTS(SELECT 1 FROM @LoseStatuses HAVING COUNT(1) = 0) OR EXISTS (SELECT 1 FROM @LoseStatuses ls WHERE ls.id = k.EndedStatus))
|
|
|
|
ORDER BY
|
|
CASE @SortOrderAsc WHEN 1 THEN
|
|
CASE WHEN ISNULL(@SortField, 1) = 1 THEN
|
|
CASE @SortField
|
|
WHEN 1 THEN CloseDate -- End time
|
|
END
|
|
END
|
|
END ASC,
|
|
|
|
CASE @SortOrderAsc WHEN 0 THEN
|
|
CASE WHEN ISNULL(@SortField, 1) = 1 THEN
|
|
CASE @SortField
|
|
WHEN 1 THEN CloseDate -- End time
|
|
END
|
|
END
|
|
END DESC,
|
|
|
|
CASE @SortOrderAsc WHEN 1 THEN
|
|
CASE WHEN ISNULL(@SortField, 1) > 1 THEN
|
|
CASE @SortField
|
|
WHEN 2 THEN CurrentProfit -- Current profit
|
|
WHEN 3 THEN Purchases -- Purchases
|
|
--WHEN 4 THEN DistanceFromTarget -- Distance from target
|
|
--WHEN 5 THEN PricePoolData -- price pool
|
|
--WHEN 6 THEN UserReview -- byer review
|
|
END
|
|
END
|
|
END ASC,
|
|
|
|
CASE @SortOrderAsc WHEN 0 THEN
|
|
CASE WHEN ISNULL(@SortField, 1) > 1 THEN
|
|
CASE @SortField
|
|
WHEN 2 THEN CurrentProfit -- Current profit
|
|
WHEN 3 THEN Purchases -- Purchases
|
|
--WHEN 4 THEN DistanceFromTarget -- Distance from target
|
|
--WHEN 5 THEN PricePoolData -- price pool
|
|
--WHEN 6 THEN UserReview -- byer review
|
|
END
|
|
END
|
|
END DESC
|
|
|
|
EXEC [BO_GetKnokChartData] @KnokId, 1, @RangeSettings
|
|
|
|
RETURN 0 |