Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Søren Linaa 246 posts 179 karma points
    Jan 30, 2020 @ 07:25
    Søren Linaa
    0

    Get orders by product

    Hi,

    I need a way to retrieve all orders which contains a product/orderline. Beacuse the customer needs to se which orders and orderdetails that have been used on specific product.

    Is there an API or another way to get all orders and filter by product ?

    / Søren

  • Matt Brailsford 2765 posts 14046 karma points MVP 7x c-trib
    Jan 30, 2020 @ 07:44
    Matt Brailsford
    0

    Hi Soren,

    We don’t have a specific function for this purpose so it would have to be a little custom. Your best bet would probably be to use TC.GetFinalizedOrdersForCustomer to get all orders for a given customer (assuming your customers are Umbraco members and they were logged in at point of purchase) and then filter them by order lines containing the given product.

    https://docs.teacommerce.net/3.4.0/api/order/#getfinalizedordersforcustomer

    Hope this helps

  • Søren Linaa 246 posts 179 karma points
    Jan 30, 2020 @ 08:04
    Søren Linaa
    0

    Hi Matt,

    Thanks you for a quick response. I red the question again after reading your answer - and maybe I misled you a bit.

    The customer is my customer 'the store manager' So he needs to see all orders and details on a product. Do you have an idea how to achieve this. If I need to get my hands dirty in the database then I need some pointers on which tables to look at.

    /Søren

  • Matt Brailsford 2765 posts 14046 karma points MVP 7x c-trib
    Jan 30, 2020 @ 08:55
    Matt Brailsford
    100

    Ahh, sorry, I thought you meant "customer" as in someone making a purchase in the store.

    In that case yea it will probably need to be custom.

    I think the best bet would bet to get a list of order IDs from the database and then use the TC API to fetch the full orders.

    I think a SQL statement like the following should get you a list of order IDs

    SELECT TCO.[Id]
    FROM [TeaCommerce_Order] as TCO
    INNER JOIN [TeaCommerce_OrderLine] as TCOL ON TCOL.[OrderId] = TCO.[Id]
    WHERE TCO.[StoreId] = YOUR_STORE_ID AND TCO.[DateFinalized] != NULL AND TCOL.[ProductIdentifier] = YOUR_PRODUCT_ID
    

    Then from there, you could call the following to retrieve the orders

    OrderService.Instance.Get(storeId, orderIds)
    

    Hope this helps

    Matt

  • Søren Linaa 246 posts 179 karma points
    Jan 30, 2020 @ 13:54
    Søren Linaa
    0

    Hi Matt

    You're awesome - thank you so much

    I made a little modification to the query, to get distinct ids and a like for productidentifier to support variants.

    SELECT distinct TCO.[Id]
    FROM [TeaCommerce_Order] as TCO
    INNER JOIN [TeaCommerce_OrderLine] as TCOL ON TCOL.[OrderId] = TCO.[Id]
    WHERE TCO.[StoreId] = 1 AND TCO.[DateFinalized] is not NULL AND TCOL.[ProductIdentifier] like '9061%'
    

    Cheers.

  • Matt Brailsford 2765 posts 14046 karma points MVP 7x c-trib
    Jan 30, 2020 @ 14:04
    Matt Brailsford
    0

    Ahh, nicely done.

    Glad we could get you what you were after 👍

Please Sign in or register to post replies

Write your reply to:

Draft