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
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 at a loss on the PHP part of it tho....
Help?