Php Drill Down Using Blueprint Example

[Jamie 1]

Has anyone tried to use the Blueprint PHP Example with the Sales Database? It looks excellent so I have been trying very hard to convert it to use for my system as a starting point. After weeks of trying I have found that the origional example that doesn'y work properly.

 

Try adding new data, all the same pages and DB from the download, but with new orders for a different year, eg 1998.

 

It will not produce the 1st child chart unless you are using a product within CatID '1'. Works great with CatId '1', try CatId '8' for instance and it won't work unless ther is a CatId '1' product on the database for that year.

 

If there is CatId '1' data, then entering Cat '8' data works except it shows for the first month of the CatId '1' data. eg, CatId '8' for Sept 1998, will show in Jan 1998 (if there is data for CatId '1' for Jan 1998).

 

As you enter more info for CatId '8' into the previous months, eg Jan, Feb, Mar etc the column will move along step by step until it reaches it's correct month.

 

I have found that entering data of '0' for every category, for every month, for every year then the system will work. I reckon it could be a grouping problem with the child chart PHP code, but it's beyond me. I thought by canibalising the blueprint example I could get going as I'm not a PHP expert, but that would've been too easy!

 

If anyone can solve it PLEASE....I am running short of time and sanity.

 

Thanks

 

Jamie

[alesnap1]

I found this code and worked fine for me...I'm trying to understand why when you try to open the "month/article detailed chart" by clicking on a month, the chart loads only CatID1....

 

//getCumulativeSalesByCatXML returns the cumulative sales for each category[/font][/color][color=#6B6B6B][font=verdana, arial, tahoma, sans-serif]//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(d.Quantity),0) as Quantity, SUM(d.Quantity*p.UnitPrice) As Total FROM FC_Categories as g,  FC_Products as p, FC_Orders as o, FC_OrderDetails as d  WHERE year(o.OrderDate)=" . $intYear ." and g.CategoryID=" . $orsCat['CategoryID'] . " and d.ProductID=p.ProductId and g.CategoryID= p.CategoryID and o.OrderID= d.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("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;

Edited February 28, 2011 by alesnap1

[alesnap1]

I noticed that now there's no more legend - category displayed.

And I noticed that in the detailed per month/category/articles chart, the chart loads only the first IDCategory.

I hope in an answer, as I can see no-one answer frequently.....

[Jamie 1]

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;

}

[Guest Angie]

Hi,

 

We are glad to know that you have managed to solve your purpose.

 

Keep FusionCharting!