Load Orders From a Date/Time Range

[paul.oreilly]

I'm trying to create logic that loads all the orders from 2 weeks prior to a given date (all the orders from that day). Ultimately we're looking to create a customer follow up workflow from these orders.

I've tried this, but it didn't work:

<mvt:comment>Assuming this is a cron job that runs at midnight, this would shave 2 weeks off the current day</mvt:comment>
<mvt:assign name="g.start_time" value="s.time_t - 1209600" />

<mvt:comment>end at the 24 hour mark</mvt:comment>
<mvt:assign name="g.end_time" value="g.start_time + (24*60*60)" />

<mvt:comment> Looking at the LSK, I thought this might work</mvt:comment>
<mvt:assign name="g.search_range" value="'WHERE orderdate BETWEEN' $ g.start_time $ 'AND'$ g.end_time " />

<mvt:do file="g.Module_Library_DB" name="l.result" value="OrderList_Load_Offset(g.Offset, g.search_range, 'orderdate', g.Max, g.NextOffset, l.settings:orders)" />

Is it possible to pass a SQL statement as the "search" parameter in the OrderList_Load_Offset function? Is there another way to get order data in a date range?

thanks for any info,
Paul

[dreamingdigital]

Why don't you try something different: Make a custom batch report that sends the email. Then manually select the orders in the order admin and run that batch report on them?

But to answer your SQL question: I doubt it. I wish the API had good examples... I have to guess like you're doing.

If you need to run SQL queries in miva you can get this module. Just be super careful!! https://github.com/ghassani/miva-sql-component

[paul.oreilly]

To be honest, I never understood batch reports, but I'll look in to it. sounds nice!

The only thing is, I'm looking to set this up as a scheduled task that runs every night without human intervention. Part of the effort is tagging contacts with what they bought, and looking at the bigger picture over time (as well as sending a prompt to review what they purchased). I'd use Mailchimp's automations to accomplish this if i could, but it's not smart enough (it leads you to believe it is, but it's not)

Anyway, I'll look closer at batch reports. That does seem to trap orders for a period of time and i see a function that deals with it.

thanks for the info!
Paul