Developer forum

Hi, I'm a DynamicWeb Newbie, but comfortable in SQL and C#.

My question is what it takes to build a reusabel list of orders/orderlines with a certain status, ordered by customers with a specific company relationship.

I could imagine a dropdown where the shopowner picks one company from a a list generated by this SQL:

   SELECT DISTINCT [AccessUserCompany] FROM [AccessUser]

..or a similar C# Linq expression, if the framework contains ORM objects for users.

The list to be generated would then hold the result of this query (or equivalent Linq expression):

SELECT U.[AccessUserName], O.[OrderID], O.[OrderDate], OL.[OrderLineProductNumber], OL.[OrderLineProductName], OL.[OrderLineQuantity]
FROM [AccessUser] U
INNER JOIN [EcomOrders] O ON O.[OrderCustomerAccessUserID] = U.[AccessUserID]
INNER JOIN [EcomOrderLines] OL ON OL.[OrderLineOrderID] = O.[OrderID]
WHERE U.[AccessUserCompany] = @PickedCompany
 AND O.[ORDERDELETED] = 0
 AND O.[OrderTransactionStatus] = 'Succeeded'
 AND O.[OrderComplete] = 'True'
 AND O.[OrderStateID] = 'OS2'
 AND OL.[OrderLineType] = 0
ORDER BY U.[AccessUserID], OL.[OrderLineProductNumber] ASC   

It would be great if the result could be grouped by AccessUserName.

Can this be achieved using lists, or does it take development of modules?

I have a running DynamicWeb site running on my Dev machine from VS2017.

Production site is running DW 9.3.11

Kind regards

Søren