55 lines
3.5 KiB
Transact-SQL
55 lines
3.5 KiB
Transact-SQL
CREATE PROCEDURE [BO_GetKnoksFull]
|
|
@KnokId BIGINT = NULL,
|
|
@UserId BIGINT = NULL,
|
|
@KnokFeedExpirationHours INT = NULL,
|
|
@RangeSettings [DurationCRTAggrItemsList] readonly
|
|
|
|
AS
|
|
SELECT k.KnokId, Currency1 Currency1, Currency2 Currency2, Currency Currency,k.ExchangeId, TickerId, CreatorUserId,
|
|
EntryPriceFrom, EntryPriceTo EntryPriceTo, ExitPriceFrom ExitPriceFrom, ExitPriceTo ExitPriceTo, StopLoss,
|
|
Duration, HasTechnical, Technical_Header, Technical_Description, Technical_References, Technical_Image_Url,
|
|
Technical_Image_FileName, HasFundamental, Fundamental_Header, Fundamental_Description, Fundamental_References,
|
|
CreateDate, PublishedDate, SellFinishDate, k.KnokStatusId, ex.ExchangeName ExchangeName, uk.BuyDate,
|
|
(SELECT lr.Rate FROM GetLastRate(k.Duration, k.Currency1, k.Currency2, k.ExchangeId) lr) AS CurrentPrice,
|
|
(SELECT lr.Rate FROM GetPrevRate(k.Duration, k.Currency1, k.Currency2, k.ExchangeId) lr) AS PrevPrice,
|
|
CAST(ISNULL(s.Decimals, 8) AS smallint) AS Precision, k.PotentialProfitValue, k.PotentialProfit,
|
|
ISNULL(purchases.UserKnoks, 0) Purchases, k.CloseDate,
|
|
(CASE
|
|
WHEN k.EntryPriceTouched = 1 AND k.ExitPriceTouched = 1 AND k.StopLossTouched = 0 THEN 4 -- Successful
|
|
WHEN k.StopLossTouched = 0 AND k.ExitPriceTouched = 0 AND status.IsClosed = 1 THEN 1 -- ExitPriceMissed
|
|
WHEN k.StopLossTouched = 1 THEN 2 -- Stop loss reached
|
|
WHEN status.IsClosed = 1 THEN 3 --ClosedByKnokser
|
|
ELSE 0 -- Unknown
|
|
END) AS EndedStatus,
|
|
-- amount of purchases * knok price * knokser commission (70%)
|
|
(SELECT SUM(pk.Price) FROM UserKnoks uk
|
|
INNER JOIN Signals pk ON pk.KnokId = uk.KnokId
|
|
WHERE pk.knokId = k.knokId
|
|
GROUP BY uk.knokId) AS PricePoolData,
|
|
ISNULL(k.UserReview, 0) AS UserReview,
|
|
feedbacks1.UserReviewClarity,
|
|
feedbacks1.UserReviewUsefulness,
|
|
feedbacks2.UserReviewProfitability,
|
|
ISNULL(feedbacks1.BuyerReviews, 0) + ISNULL(feedbacks2.BuyerReviews, 0) AS BuyerReviews,
|
|
(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 [dbo].[Signals] k with(nolock)
|
|
left join [dbo].[Exchanges] ex on ex.ExchangeId =k.ExchangeId
|
|
left join [dbo].[UserKnoks] uk on uk.KnokId =@KnokId AND uk.UserId= @UserId
|
|
left join [dbo].[Symbols] s on s.Symbol = k.Currency2
|
|
LEFT JOIN (select count(f.UserId) UserKnoks, f.KnokId KnokId from [dbo].[UserKnoks] f group by f.KnokId) purchases on purchases.KnokId = k.KnokId
|
|
LEFT JOIN [dbo].[_KnokStatuses] status on k.KnokStatusId = status.KnokStatusId
|
|
LEFT JOIN (select COUNT(1) AS BuyerReviews, AVG(CAST(fb1.[Clear] as decimal(22, 2))) AS [UserReviewClarity], AVG(CAST(fb1.[Comprehensive ] as decimal(22, 2))) AS UserReviewUsefulness, fb1.KnokId KnokId from [dbo].[TraderFeedbacks] fb1 where fb1.FeedbackType = 0 group by fb1.KnokId) feedbacks1 on feedbacks1.KnokId = k.KnokId
|
|
LEFT JOIN (select COUNT(1) AS BuyerReviews, AVG(CAST(fb2.[KnokBenefit] as decimal(22, 2))) AS UserReviewProfitability, fb2.KnokId KnokId from [dbo].[TraderFeedbacks] fb2 where fb2.FeedbackType = 1 group by fb2.KnokId) feedbacks2 on feedbacks2.KnokId = k.KnokId
|
|
WHERE k.KnokId = ISNULL(@KnokId, k.KnokId)
|
|
|
|
DECLARE @ShowEndedOnly BIT = NULL
|
|
|
|
SET @ShowEndedOnly = (SELECT CASE WHEN k.KnokStatusId in (5, 6) THEN 1 ELSE 0 END
|
|
FROM [dbo].[Signals] k with(nolock)
|
|
WHERE k.KnokId = ISNULL(@KnokId, k.KnokId))
|
|
|
|
EXEC [BO_GetKnokChartData] @KnokId, @ShowEndedOnly, @RangeSettings
|
|
RETURN 0
|