$VAR1 = {
          'columnsettings' => 'stype,C.Center

cname,Category

siname,Item

unit,Units

qty,Total Qty

lastprice,Last Price

maximumprice,Max Price

minprice,Min Price

avgprice,Avg Price',
          'selector' => '2',
          'data' => 'SELECT st.name stype, sc.name cname, si.name siname, 

	CASE si.units 

		WHEN 1 THEN \'kg\'

		WHEN 2 THEN \'litre\'

		WHEN 3 THEN \'each\'

	END unit,

	SUM(org_qty) qty,

	

	ROUND(SUBSTRING_INDEX(GROUP_CONCAT(pu.price ORDER BY pu.date DESC), \',\', 1),2) lastprice,

	ROUND(MAX(pu.price) , 2) maximumprice,

	ROUND(MIN(pu.price) , 2) minprice,

	ROUND(AVG(pu.price) , 2) avgprice

	

	FROM 

		purchases pu,

		stockitems si,

		stocktypes st,

		stockcats sc



	WHERE si.id = pu.stockitems_id

	AND si.type = st.id AND si.stockcats_id = sc.id

	AND si.stockitem = 1



GROUP BY si.id



ORDER BY st.name,sc.name, si.name;',
          'description' => 'Last, Min, Avg Unit Price',
          'settings' => '',
          'rollup' => '',
          'id' => '21',
          'title' => 'Unit Price Stats',
          'category' => 'PURCHASES',
          'code' => '21'
        };