Posted on 17/12/2024 16:00:36
Hi,
I do believe this issue is the same across DW9 and DW10.
When using the below option, I checked with debug=true and can confirm that the SQL query checks for OrderSecondaryUserId of EcomOrders, instead of matching OrderCustomerUserId from ImpersonatedUsers.
I do believe this is inconsistant with the wording. I have multiple projects that are planned to use the functionality as it's worded. I suggest fixing this bug with adding a 4th option, using the text from the selection option. And rephrasing the text to say "Own orders, and orders made while impersonating".
I added the SQL extract below, and highlighted the line in fault:
DECLARE @p0 DATETIME = '2024-12-09 16:14:27.283',
@p1 INT = 207,
@p2 INT = 1,
@p3 INT = 3,
@p4 INT = 5,
@p5 NVARCHAR(5) = N'LANG1',
@p6 DATETIME = '9999-12-31 00:00:00.000',
@p7 NVARCHAR(6) = N'SHOP14',
@p8 INT = 0,
@p9 INT = 1,
@p10 INT = 10
DECLARE @DefaultRate float;
SET ARITHABORT OFF;
SET @DefaultRate = IsNull((SELECT TOP 1 CurrencyRate FROM EcomCurrencies WHERE CurrencyIsDefault = 1), 100);
DECLARE @Time datetime;
SET @Time = @p0;
WITH ImpersonationIds AS
(
SELECT AccessUserSecondaryRelationSecondaryUserId AS UserId FROM AccessUserSecondaryRelation
LEFT JOIN AccessUser ON AccessUserId = AccessUserSecondaryRelationSecondaryUserId WHERE AccessUserSecondaryRelationUserId = @p1 AND AccessUserType IN (@p2,@p3,@p4)
UNION ALL SELECT @p1 UNION ALL SELECT AccessUserId FROM AccessUser
WHERE EXISTS
(
SELECT * FROM AccessUserSecondaryRelation
WHERE AccessUserSecondaryRelationUserId = @p1
AND AccessUserGroups LIKE '%@' + CONVERT(varchar(50), AccessUserSecondaryRelationSecondaryUserId) + '@%'
)
),
OrderedOrders AS
(
SELECT ROW_NUMBER() OVER(ORDER BY OrderDate Desc) AS RowNumber, EcomOrders.OrderId, EcomOrders.OrderShopName, EcomRecurringOrder.* , AccessUser.AccessUserName AS OrderSecondaryUserName
FROM EcomOrders WITH (NOLOCK)
LEFT JOIN EcomCurrencies ON EcomOrders.OrderCurrencyCode = EcomCurrencies.CurrencyCode AND EcomCurrencies.CurrencyLanguageID = @p5
LEFT JOIN EcomOrderStates ON EcomOrders.OrderStateID = EcomOrderStates.OrderStateID
LEFT JOIN EcomShops ON EcomOrders.OrderShopID = EcomShops.ShopID
LEFT JOIN EcomRmas ON EcomOrders.OrderID = EcomRmas.RmaReplacementOrderId
LEFT JOIN AccessUser ON EcomOrders.OrderSecondaryUserID = AccessUser.AccessUserID
LEFT JOIN EcomRecurringOrder ON EcomOrders.OrderRecurringOrderId = EcomRecurringOrder.RecurringOrderID
LEFT JOIN EcomTrackAndTrace ON EcomOrders.OrderTrackAndTraceId = EcomTrackAndTrace.TrackAndTraceId
WHERE EcomOrders.OrderDeleted = 0
AND EcomOrders.OrderDate <= @p6
AND EcomOrders.OrderShopID = @p7
AND EcomOrders.OrderComplete = 0
AND EcomOrders.OrderIsQuote = 1
AND EcomOrders.OrderIsLedgerEntry = @p8
AND (EcomOrders.OrderIsRecurringOrderTemplate = 0 or OrderIsRecurringOrderTemplate is NULL)
AND EXISTS
(
SELECT * FROM ImpersonationIds
WHERE ImpersonationIds.UserId = EcomOrders.OrderCustomerAccessUserID
OR ImpersonationIds.UserId = EcomOrders.OrderSecondaryUserId
)
), OrdersCount as
(
SELECT COUNT(*) AS TotalOrdersCount FROM OrderedOrders
)
SELECT * FROM OrdersCount, OrderedOrders WITH (NOLOCK)
WHERE RowNumber BETWEEN @p9 AND @p10
ORDER BY RowNumber
Red markings should be deleted
Yellow should be rewritten, to consider data from AccessUserGroupRelation aswell as AccessUserGroups from AccessUser. In conclusion, it should be able to look for User to Group impersonation relations in addition to User to User impersonations.
/Kenneth
