Now I am working on two reports. The first one prompts for a date range and then takes the item name, sum of item quantity, and sum of item cost for each item type in the table and prints it to screen.
Eg -
- Code: Select all
Part Number Total Cost
5551111 52 $5400.24
662100-1 4 $36121.45
and so forth......
Here was the SQL code from the NB2.0 version:
- Code: Select all
$startdate = '#from_date#';
$enddate = '#to_date#';
$ord='2013_orders';
$sql = "CREATE TEMPORARY TABLE tmp1 SELECT $ord.item, $ord.price FROM $ord WHERE $ord.odate >= '$startdate' and $ord.odate <= '$enddate' ORDER BY item";
nuRunQuery($sql);
$sql = "CREATE TEMPORARY TABLE tmp2 SELECT item, SUM(price) AS total from tmp1 GROUP BY item WITH ROLLUP ";
nuRunQuery($sql);
$sql = "UPDATE tmp2 SET item='0' WHERE tmp2.item='NULL'";
nuRunQuery($sql);
$sql = "CREATE TEMPORARY TABLE tmp3 SELECT item, COUNT(*) AS qty from tmp1 GROUP BY item WITH ROLLUP ";
nuRunQuery($sql);
$sql = "CREATE TABLE #dataTable# SELECT tmp2.item, tmp3.qty, tmp2.total FROM tmp2, tmp3 WHERE tmp2.item=tmp3.item ";
nuRunQuery($sql);
I am guessing I will need to make a select_date form (and table) and then have PHP run the item tallies.
I am at a loss on the PHP part of it tho....
Help?