Thankyou Very Much my man!!! That works for me , after weeks of trying.
I had to alter the code slightly because I've altered my DB tables a bit, but worked great after that.
My code from the top to the end of the Product chart is below if that helps you. Otherwise look in the DataGen.php or Data_SalesByCategory. I think the system is great but pretty complicated too.
I can post more of my code if it will help.
Thanks again buddy.
<?php
//This page contains functions which generate the XML data for the chart.
//Effectively, we've separated this part from each PHP page to simulate a
//3-tier architecture. In real world, you could replace this by a COM
//Component or similar technologies which would connect to the database and
//return data (in XML or normally, which could later be converted to XML).
//getSalesByYear function returns the XML for yearly sales figures (including
//quantity)
function getSalesByYear() {
// Function to connect to the DB
$link = connectToDB();
//Initialize <categories> element
$strCat = "<categories>";
//Initialize datasets
$strAmtDS = "<dataset seriesname='Downtime'>";
$strQtyDS = "<dataset seriesName='Occasions' parentYAxis='S'>";
$strSQL = "SELECT Year(o.OrderDate) As SalesYear, ROUND(SUM(d.Quantity*o.UnitPrice),0) As Total, SUM(d.Quantity) as Quantity FROM FC_Orders as d,FC_Orders as o,FC_Products as p WHERE o.OrderID=d.OrderID and d.ProductID=p.ProductID GROUP BY Year(o.OrderDate) ORDER BY Year(o.OrderDate)";
$result = mysql_query($strSQL) or die(mysql_error());
if ($result) {
while($ors = mysql_fetch_array($result)) {
$strCat .= "<category label='" . $ors['SalesYear'] . "'/>";
//Generate the link
$strLink = urlencode("javaScript:updateCharts(" . $ors['SalesYear'] . ");");
$strAmtDS .= "<set value='" . $ors['Total'] . "' link='" . $strLink . "'/>";
$strQtyDS .= "<set value='" . $ors['Quantity'] . "'/>";
}
}
mysql_close($link);
//Closing elements
$strCat .= "</categories>";
$strAmtDS .= "</dataset>";
$strQtyDS .= "</dataset>";
//Entire XML - concatenation
$strXML = $strCat . $strAmtDS . $strQtyDS;
return $strXML;
}
//getCumulativeSalesByCatXML returns the cumulative sales for each category[/font][/color]//in a given year
function getCumulativeSalesByCatXML($intYear, $forDataURL) {
// Function to connect to the DB
$link = connectToDB();
//To store categories - also flag to check whether category is
//already generated
//Initialize XML elements
//Categories
$strCat = "<categories>";
$arrCat =array();
$strSQL = "SELECT distinct Month(o.OrderDate) as MonthNum from FC_Orders as o WHERE year(o.OrderDate)=$intYear order by Month(o.OrderDate)";
$result = mysql_query($strSQL) or die(mysql_error());
if ($result) {
$mc=0;
while($orsCat = mysql_fetch_array($result)) {
//Add this category as dataset
$arrCat[$mc++]=date("n",mktime(0,0,0,$orsCat['MonthNum'],2,1994));
$strCat .= "<category label='" . date("F",mktime(0,0,0,$orsCat['MonthNum'],2,1994)) . "' />";
}
mysql_free_result($result);
}
$strCat .= "</categories>";
//First we need to get unique categories in the database
$strSQL = "Select CategoryID,CategoryName from FC_Categories GROUP BY CategoryID,CategoryName";
$result = mysql_query($strSQL) or die(mysql_error());
//To store datasets and sets
$strDataXML = "";
if ($result) {
while($orsCat = mysql_fetch_array($result)) {
//Add this category as dataset
$strDataXML .= "<dataset seriesName='" . escapeXML($orsCat['CategoryName'],$forDataURL) . "'>";
//Now, we need to get monthly sales data for products in this category
$strSQL = "SELECT Month(o.OrderDate) as MonthNum, g.CategoryID, g.CategoryName, ROUND(SUM(o.Quantity),0) as Quantity, SUM(o.Quantity*o.UnitPrice) As Total FROM FC_Categories as g, FC_Products as p, FC_Orders as o WHERE year(o.OrderDate)=" . $intYear ." and g.CategoryID=" . $orsCat['CategoryID'] . " and o.ProductID=p.ProductId and g.CategoryID= p.CategoryID and o.OrderID= o.OrderID GROUP BY g.CategoryID,g.CategoryName,Month(o.OrderDate) order by Month(o.OrderDate)";
//Execute it
$result2 = mysql_query($strSQL) or die(mysql_error());
$mc=0;
while($ors = mysql_fetch_array($result2)) {
//Append <category label=''> if not already done
//Generate the link
$strLink = urlencode("javaScript:updateProductChart(" . $intYear . "," . $ors['MonthNum'] . "," . $ors['CategoryID'] . ");");
while($arrCat[$mc++]<$ors["MonthNum"]){
$strDataXML .="<set/>";
}
$strDataXML .= "<set value='" . $ors['Total'] . "' link='" . $strLink . "'/>";
}
//Clear up objects
mysql_free_result($result2);
//Close dataset element
$strDataXML .= "</dataset>";
}
}
mysql_close($link);
//Create full XML
$strXML = $strCat . $strDataXML;
//Return
return $strXML;
}
//getSalesByProdXML returns the sales for the products within a category
//for a given year and month
function getSalesByProdXML($intYear, $intMonth, $intCatId, $forDataURL) {
// Function to connect to the DB
$link = connectToDB();
//Initialize <categories> element
$strCat = "<categories>";
//Initialize datasets
$strAmtDS = "<dataset seriesname='Downtime mins'>";
$strQtyDS = "<dataset seriesName='Occasions' parentYAxis='S'>";
//First we need to get unique categories in the database
$strSQL = "SELECT g.CategoryName,p.ProductName, p.ProductID, ROUND(SUM(d.Quantity),0) as Quantity, ROUND(SUM(d.Quantity*o.UnitPrice),0) As Total FROM FC_Categories as g, FC_Products as p, FC_Orders as o, FC_Orders as d WHERE year(o.OrderDate)=" . $intYear . " and month(o.OrderDate)=" . $intMonth . " and g.CategoryID=" . $intCatId . " and d.ProductID= p.ProductID and g.CategoryID= p.CategoryID and o.OrderID= d.OrderID GROUP BY g.CategoryName,p.ProductName ";
$result = mysql_query($strSQL) or die(mysql_error());
if ($result) {
while($ors = mysql_fetch_array($result)) {
$strCat .= "<category label='" . escapeXML($ors['ProductName'],$forDataURL) . "'/>";
$strAmtDS .= "<set value='" . $ors['Total'] . "'link='" . urlencode("Default2.php?ProductID=" . $ors['ProductID']) . "' />";
$strQtyDS .= "<set value='" . $ors['Quantity'] . "'/>";
}
}
mysql_close($link);
//Closing elements
$strCat .= "</categories>";
$strAmtDS .= "</dataset>";
$strQtyDS .= "</dataset>";
//Entire XML - concatenation
$strXML = $strCat . $strAmtDS . $strQtyDS;
return $strXML;
}
