RE: can you solve this problem?

Sudipto Choudhury — Mon, 28 Apr 2008 09:25:30 GMT

Hi,

Could you please try this code as things can be done in various flavours...

//getCumulativeSalesByCatXML returns the cumulative sales for each category
//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)";
$resultCat = mysql_query($strSQL) or die(mysql_error());
if ($resultCat) {
while($orsCat = mysql_fetch_array($resultCat)) {
//Add this category as dataset
$strCat .= "<category label='" . date("F",mktime(0,0,0,$orsCat['MonthNum'],2,1994)) . "' />";
}

}
$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) . "'>";

mysql_data_seek($resultCat,0);
while($orsCat1=mysql_fetch_array($resultCat)){

//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 Month(o.OrderDate)=". $orsCat1["MonthNum"] ." 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());
$ors = mysql_fetch_array($result2);

if($ors==false){
$strDataXML .="<set value=''/>";

}else{
$strLink = urlencode("updateProductChart(" . $intYear . "," . $ors['MonthNum'] . "," . $ors['CategoryID'] . ");");
$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;
}

RE: can you solve this problem?

maheshshinde9 — Mon, 28 Apr 2008 07:04:48 GMT

RE: can you solve this problem?

maheshshinde9 — Mon, 28 Apr 2008 07:03:21 GMT

But the problem with that code is that it does not show zero (0) for november month.

Means First category (i.e Blue chart) have data only upto august month(i.e 8th month).

So, while loop never get executed for month 11.

Months (label) - Feb,mar,apr,may,june,july,august,november

1st category have data for months- feb,mar,apr,may,june,july,august

2nd category have data for months - feb,mar,apr,may,june,november

means for 1st category while loop process as follows:

while(2<2) ,while(3<3),while(4<4),while(5<5) ,while(6<6) ,while(7<7),while(8<8)

But after that while loop for month 11 does not execute because 1st category have data

up to 8th month. thats why it does not show zero there .can you tell me solution?

can you solve this problem?

maheshshinde9 — Mon, 28 Apr 2008 06:55:09 GMT

I have set the following code to display 0 for particular bar (same code that you have posted to particular thread)

while($arrCat[$mc++]<$ors{'months1'])

{

$strDataXML .="<set value='0.00001'/>";

}

FusionCharts Forum / FusionCharts Free / FusionCharts Free and PHP / can you solve this problem? / Latest Posts

RE: can you solve this problem?

RE: can you solve this problem?

RE: can you solve this problem?

can you solve this problem?