Working on the latest version on Epi/Episerver Commerce.
I am having to create some custom reports for our client. One of the reports requires the following:
Each order can have split shipments, so different warehouses (stores) fulfilling orders. Looking at the database schema https://world.episerver.com/globalassets/sdkdocuments/developers-guide1/episerver-commerce/75/orders/ordersystem.png
I can't see any direct relationship between shipment and lineitem tables. As warehousecode in the LineItems table is now obsolete we would now have to use the Shipment table to find out the warehouse/store.
So the question is, at a database level, how can I found out which split shipment is shipping which lineitem so I can work out which warehouse/store is dealing with that lineitem.
I would suggest to go with apis approach instead of querying database directly. It is safer and more future proof. The relationships between shipments and lineitems are handled at api level anyway
Thanks, while going down the API root is possible these are going to be heavy queries and surely benifit from query execution plans and optimisation. I'm not sure how well episerver could handle thousands and thousands of orders?
For the purpose of report which is usually run only daily at low traffic, loading orders by batches of thousands should not be a problem
I cannot guarantee when the reports will be ran! And as the requirements are for the report to be outputted into CSV there will be no pagination, so in the case all orders (between any possible date range) will be loaded. This is my concern.
I don't get it. What is the problem with loading by batches and process data and save to the csv files when you are done with a batch?
As you can see its going to be pretty heavy on the database!
Reporting is supposed to be data heavy. Unless you run your report every few minutes it would not be a problem. I don't know what you are doing with your reports but sales data can "appended" - for example daily, instead of getting full data every time