I've been pretty wrapped up for the past couple of days and didn't get a chance, until now, to post that I got it working.
My PHP is rather rusty since I haven't had to use it in forever, but I knocked enough rust off to get this going. I was under a time limit since my old machine was dying a slow, yet accelerating death. I usually keep knocking at a problem until I figure it out
(which is what wound up happening anyway), but I was hoping someone would have put me in the "You idiot" spotlight and posted up the code lol. I would have gladly accepted my 15 minutes of humble fame.
Here's the code in case someone else is searches for an answer to a similar question:
Code: Select all
$sql = "
CREATE TABLE #TABLE_ID#
SELECT
item, COUNT(*) AS Total, SUM(price) AS Total_Price
SUM(Total_Price) AS Total_Costs
FROM 2013_orders
WHERE odate BETWEEN '#from_date#' AND '#to_date#'
GROUP By item
";
nuRunQuery($sql);
That gave me everything I needed to get the report to display all items in the date range correctly.
A little creative editing the database itself got my date format worked out.
I did not use really anything from the INVLIST report as it wasn't geared for this. It's geared for another type of query and does it beautifully. Since I didn't need to incorporate a filter function, I created my own date range select form to keep it simple.
I will be using INVLIST as a guide to make a report that will show me all open orders, but that shouldn't be as much of a headache now...except for that fact that I will need that one to export to a CSV file. I am hoping that's covered in the wiki.