Shipment Lineitems

Vote:
 

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:

  • PO Number
  • Date of order
  • Warehouse
  • Delivery status
  • Payment status
  • Total (item cost in £s)
  • Postage (in £s)
  • Gross (Total + postage)
  • Refunds (in £s)

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.

#204117
May 20, 2019 12:00
Vote:
 

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

#204120
May 20, 2019 12:34
Vote:
 

@Quan

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?

#204121
May 20, 2019 12:37
Vote:
 

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

#204129
May 20, 2019 13:22
Vote:
 

Hi Quan,

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.

#204131
May 20, 2019 13:25
Vote:
 

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?

#204133
May 20, 2019 13:29
Vote:
 
  • Hit the database to get total rows using FindPurchaseOrders, this in turn calls [ecf_Search_PurchaseOrder] which in turn calls [mdpsp_avto_OrderGroup_PurchaseOrder_Search]
  • Hit the database in calling [ecf_Search_PurchaseOrder] which in turn calls [mdpsp_avto_OrderGroup_PurchaseOrder_Search] with search parameters
  • Repeat the above step multiple times per batch
  • Iterate/transform results

As you can see its going to be pretty heavy on the database!

#204134
May 20, 2019 13:36
Vote:
 

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

#204136
May 20, 2019 14:34