<%
'In this page, we open the connection to the Database
'Our Access database is contained in ../DB/FactoryDB.mdb
'It's a very simple database with just 2 tables (for the sake of demo)
Dim oConn
'If not already defined, create object
if not isObject(oConn) then
Dim strConnQuery
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Mode = 3
'Create the path to database
strConnQuery = "DBQ=" & server.mappath("../DB/FactoryDB.mdb")
'Connect
oConn.Open("DRIVER={Microsoft Access Driver (*.mdb)}; " & strConnQuery)
end if
%>
FusionCharts.asp
<%
'Page: FusionCharts.asp
'Author: InfoSoft Global (P) Ltd.
'This page contains functions that can be used to render FusionCharts.
'encodeDataURL function encodes the dataURL before it's served to FusionCharts.
'If you've parameters in your dataURL, you necessarily need to encode it.
'Param: strDataURL - dataURL to be fed to chart
'Param: addNoCacheStr - Whether to add aditional string to URL to disable caching of data
Function encodeDataURL(strDataURL, addNoCacheStr)
'Add the no-cache string if required
if addNoCacheStr=true then
'We add ?FCCurrTime=xxyyzz
'If the dataURL already contains a ?, we add &FCCurrTime=xxyyzz
'We replace : with _, as FusionCharts cannot handle : in URLs
if Instr(strDataURL,"?")<>0 then
strDataURL = strDataURL & "&FCCurrTime=" & Replace(Now(),":","_")
else
strDataURL = strDataURL & "?FCCurrTime=" & Replace(Now(),":","_")
end if
end if
'URL Encode it
encodeDataURL = Server.URLEncode(strDataURL)
End Function
'renderChart renders the JavaScript + HTML code required to embed a chart.
'This function assumes that you've already included the FusionCharts JavaScript class
'in your page.
' chartSWF - SWF File Name (and Path) of the chart which you intend to plot
' strURL - If you intend to use dataURL method for this chart, pass the URL as this parameter. Else, set it to "" (in case of dataXML method)
' strXML - If you intend to use dataXML method for this chart, pass the XML data as this parameter. Else, set it to "" (in case of dataURL method)
' chartId - Id for the chart, using which it will be recognized in the HTML page. Each chart on the page needs to have a unique Id.
' chartWidth - Intended width for the chart (in pixels)
' chartHeight - Intended height for the chart (in pixels)
' debugMode - Whether to start the chart in debug mode
' registerWithJS - Whether to ask chart to register itself with JavaScript
Function renderChart(chartSWF, strURL, strXML, chartId, chartWidth, chartHeight, debugMode, registerWithJS)
'First we create a new DIV for each chart. We specify the name of DIV as "chartId"Div.
'DIV names are case-sensitive.
%>
<!-- START Script Block for Chart <%=chartId%> -->
<div id='<%=chartId%>Div' align='center'>
Chart.
<%
'The above text "Chart" is shown to users before the chart has started loading
'(if there is a lag in relaying SWF from server). This text is also shown to users
'who do not have Flash Player installed. You can configure it as per your needs.
%>
</div>
<%
'Now, we render the chart using FusionCharts Class. Each chart's instance (JavaScript) Id
'is named as chart_"chartId".
%>
<script type="text/javascript">
//Instantiate the Chart
var chart_<%=chartId%> = new FusionCharts("<%=chartSWF%>", "<%=chartId%>", "<%=chartWidth%>", "<%=chartHeight%>", "<%=boolToNum(debugMode)%>", "<%=boolToNum(registerWithJS)%>");
<%
'Check whether we've to provide data using dataXML method or dataURL method
if strXML="" then %>
//Set the dataURL of the chart
chart_<%=chartId%>.setDataURL("<%=strURL%>");
<% else %>
//Provide entire XML data using dataXML method
chart_<%=chartId%>.setDataXML("<%=strXML%>");
<% end if %>
//Finally, render the chart.
chart_<%=chartId%>.render("<%=chartId%>Div");
</script>
<!-- END Script Block for Chart <%=chartId%> -->
<%
End Function
'renderChartHTML function renders the HTML code for the JavaScript. This
'method does NOT embed the chart using JavaScript class. Instead, it uses
'direct HTML embedding. So, if you see the charts on IE 6 (or above), you'll
'see the "Click to activate..." message on the chart.
' chartSWF - SWF File Name (and Path) of the chart which you intend to plot
' strURL - If you intend to use dataURL method for this chart, pass the URL as this parameter. Else, set it to "" (in case of dataXML method)
' strXML - If you intend to use dataXML method for this chart, pass the XML data as this parameter. Else, set it to "" (in case of dataURL method)
' chartId - Id for the chart, using which it will be recognized in the HTML page. Each chart on the page needs to have a unique Id.
' chartWidth - Intended width for the chart (in pixels)
' chartHeight - Intended height for the chart (in pixels)
' debugMode - Whether to start the chart in debug mode
Function renderChartHTML(chartSWF, strURL, strXML, chartId, chartWidth, chartHeight, debugMode)
'Generate the FlashVars string based on whether dataURL has been provided
'or dataXML.
Dim strFlashVars
if strXML="" then
'DataURL Mode
strFlashVars = "&chartWidth=" & chartWidth & "&chartHeight=" & chartHeight & "&debugMode=" & boolToNum(debugMode) & "&dataURL=" & strURL
else
'DataXML Mode
strFlashVars = "&chartWidth=" & chartWidth & "&chartHeight=" & chartHeight & "&debugMode=" & boolToNum(debugMode) & "&dataXML=" & strXML
end if
%>
<!-- START Code Block for Chart <%=chartId%> -->
<object classid="clsid:d27cdb6e-ae6d-11cf-96b8-444553540000" codebase="http://fpdownload.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=8,0,0,0" width="<%=chartWidth%>" height="<%=chartHeight%>" id="<%=chartId%>">
<param name="allowScriptAccess" value="always" />
<param name="movie" value="<%=chartSWF%>"/>
<param name="FlashVars" value="<%=strFlashVars%>" />
<param name="quality" value="high" />
<embed src="<%=chartSWF%>" FlashVars="<%=strFlashVars%>" quality="high" width="<%=chartWidth%>" height="<%=chartHeight%>" name="<%=chartId%>" allowScriptAccess="always" type="application/x-shockwave-flash" pluginspage="http://www.macromedia.com/go/getflashplayer" />
</object>
<!-- END Code Block for Chart <%=chartId%> -->
<%
End Function
'boolToNum function converts boolean values to numeric (1/0)
Function boolToNum(bVal)
Dim intNum
if bVal=true then
intNum = 1
else
intNum = 0
end if
boolToNum = intNum
End Function
%>
Default.asp
<%@ Language=VBScript %>
<HTML>
<HEAD>
<TITLE>
FusionCharts - Database and Drill-Down Example
</TITLE>
<%
'You need to include the following JS file, if you intend to embed the chart using JavaScript.
'Embedding using JavaScripts avoids the "Click to Activate..." issue in Internet Explorer
'When you make your own charts, make sure that the path to this JS file is correct. Else, you would get JavaScript errors.
%>
<SCRIPT LANGUAGE="Javascript" SRC="../../FusionCharts/FusionCharts.js"></SCRIPT>
<style type="text/css">
<!--
body {
font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
}
.text{
font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
}
-->
</style>
</HEAD>
<%
'We've included ../Includes/FusionCharts.asp, which contains functions
'to help us easily embed the charts.
%>
<!-- #INCLUDE FILE="../Includes/FusionCharts.asp" -->
<!-- #INCLUDE FILE="../Includes/DBConn.asp" -->
<BODY>
<CENTER>
<h2>FusionCharts Database and Drill-Down Example</h2>
<h4>Click on any pie slice to see detailed data.</h4>
<p class='text'>Or, right click on any pie to enable slicing or rotation mode. </p>
<%
'In this example, we show how to connect FusionCharts to a database.
'For the sake of ease, we've used an Access database which is present in
'../DB/FactoryDB.mdb. It just contains two tables, which are linked to each
'other.
'Database Objects - Initialization
Dim oRs, oRs2, strQuery
'strXML will be used to store the entire XML document generated
Dim strXML
'We also keep a flag to specify whether we've to animate the chart or not.
'If the user is viewing the detailed chart and comes back to this page, he shouldn't
'see the animation again.
Dim animateChart
animateChart = Request.QueryString("animate")
'Set default value of 1
if animateChart="" then
animateChart = "1"
end if
'Create the recordset to retrieve data
Set oRs = Server.CreateObject("ADODB.Recordset")
'Generate the chart element
strXML = "<chart caption='Factory Output report' subCaption='By Quantity' pieSliceDepth='30' showBorder='1' formatNumberScale='0' numberSuffix=' Units' animation=' " & animateChart & "'>"
'Iterate through each factory
strQuery = "select * from Factory_Master"
Set oRs = oConn.Execute(strQuery)
While Not oRs.Eof
'Now create second recordset to get details for this factory
Set oRs2 = Server.CreateObject("ADODB.Recordset")
strQuery = "select sum(Quantity) as TotOutput from Factory_Output where FactoryId=" & ors("FactoryId")
Set oRs2 = oConn.Execute(strQuery)
'Generate <set label='..' value='..' link='..' />
'Note that we're setting link as Detailed.asp?FactoryId=<<FactoryId>>
strXML = strXML & "<set label='" & ors("FactoryName") & "' value='" & ors2("TotOutput") & "' link='" & Server.URLEncode("Detailed.asp?FactoryId=" & ors("FactoryId")) & "'/>"
'Close recordset
Set oRs2 = Nothing
oRs.MoveNext
Wend
'Finally, close <chart> element
strXML = strXML & "</chart>"
Set oRs = nothing
'Create the chart - Pie 3D Chart with data from strXML
Call renderChart("../../FusionCharts/Pie3D.swf", "", strXML, "FactorySum", 600, 300, false, false)
%>
<BR><BR>
<a href='../NoChart.html' target="_blank">Unable to see the chart above?</a>
</CENTER>
</BODY>
</HTML>
Detailed.asp
<%@ Language=VBScript %>
<HTML>
<HEAD>
<TITLE>
FusionCharts - Database and Drill-Down Example
</TITLE>
<%
'You need to include the following JS file, if you intend to embed the chart using JavaScript.
'Embedding using JavaScripts avoids the "Click to Activate..." issue in Internet Explorer
'When you make your own charts, make sure that the path to this JS file is correct. Else, you would get JavaScript errors.
%>
<SCRIPT LANGUAGE="Javascript" SRC="../../FusionCharts/FusionCharts.js"></SCRIPT>
<style type="text/css">
<!--
body {
font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
}
.text{
font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
}
-->
</style>
</HEAD>
<%
'We've included ../Includes/FusionCharts.asp, which contains functions
'to help us easily embed the charts.
%>
<!-- #INCLUDE FILE="../Includes/FusionCharts.asp" -->
<!-- #INCLUDE FILE="../Includes/DBConn.asp" -->
<BODY>
<CENTER>
<h2>FusionCharts Database and Drill-Down Example</h2>
<h4>Detailed report for the factory</h4>
<%
'This page is invoked from Default.asp. When the user clicks on a pie
'slice in Default.asp, the factory Id is passed to this page. We need
'to get that factory id, get information from database and then show
'a detailed chart.
'First, get the factory Id
Dim FactoryId
'Request the factory Id from Querystring
FactoryId = Request.QueryString("FactoryId")
Dim oRs, strQuery
'strXML will be used to store the entire XML document generated
Dim strXML, intCounter
intCounter = 0
Set oRs = Server.CreateObject("ADODB.Recordset")
'Generate the chart element string
strXML = "<chart palette='2' caption='Factory " & FactoryId &" Output ' subcaption='(In Units)' xAxisName='Date' showValues='1' labelStep='2' >"
'Now, we get the data for that factory
strQuery = "select * from Factory_Output where FactoryId=" & FactoryId
Set oRs = oConn.Execute(strQuery)
While Not oRs.Eof
'Here, we convert date into a more readable form for set label.
strXML = strXML & "<set label='" & datePart("d",ors("DatePro")) & "/" & datePart("m",ors("DatePro")) & "' value='" & ors("Quantity") & "'/>"
Set oRs2 = Nothing
oRs.MoveNext
Wend
'Close <chart> element
strXML = strXML & "</chart>"
Set oRs = nothing
'Create the chart - Column 2D Chart with data from strXML
Call renderChart("../../FusionCharts/Column2D.swf", "", strXML, "FactoryDetailed", 600, 300, false, false)
%>
<BR>
<a href='Default.asp?animate=0'>Back to Summary</a>
<BR><BR>
<a href='../NoChart.html' target="_blank">Unable to see the chart above?</a>
</CENTER>
</BODY>
</HTML>
public string PieChart3D(string OfficeName)
{
//In this example, we plot a single series chart from data contained
//in an array. The array will have two columns - first one for data label
//and the next one for data values.
//Let's store the sales data for 6 products in our array). We also store
//the name of products.
List<string> arrDataName = new List<string>();
List<string> arrDataValue = new List<string>();
List<string> arrDataTooltip = new List<string>();
//Store Name of Products
DataTable DtName = new DataTable();
if (Request.QueryString["div"] != null)
{
DtName = KA.KeyAccountByOfficeCode(Request.QueryString["div"].ToString(), DateTime.Parse(SelectMonth.Text).Month, AD);
foreach (DataRow Dr in DtName.Rows)
{
arrDataName.Add(Dr["name_t"].ToString());
}
}
else if (Request.QueryString["dep"] != null)
{
DtName = MyOffice.GetOfficeByRelation(Request.QueryString["dep"].ToString(), true);
foreach (DataRow Dr in DtName.Rows)
{
arrDataName.Add(Dr["OfficeShortName"].ToString());
}
}
else
{
DtName = MyOffice.GetOfficeByRelation("D0005", true);
foreach (DataRow Dr in DtName.Rows)
{
arrDataName.Add(Dr["OfficeShortName"].ToString());
}
}
//Store sales data
float Value = 100 / DtName.Rows.Count;
if (Request.QueryString["div"] != null)
{
int AllActivity = 0;
foreach (DataRow Dr in DtName.Rows)
{
AllActivity += Activity.AllActivity(Dr["USerID"].ToString(), DateTime.Parse(SelectMonth.Text).Month, AD).Rows.Count;
}
if (AllActivity > 0)
{
foreach (DataRow Dr in DtName.Rows)
{
int Act = Activity.AllActivity(Dr["USerID"].ToString(), DateTime.Parse(SelectMonth.Text).Month, AD).Rows.Count;
float ActPersent = (100 * Act) / AllActivity;
arrDataValue.Add(ActPersent.ToString());
arrDataTooltip.Add(Dr["name_t"].ToString() + ", " + Act.ToString() + " ครั้ง");
}
}
else
{
foreach (DataRow Dr in DtName.Rows)
{
arrDataValue.Add(Value.ToString());
arrDataTooltip.Add(Dr["name_t"].ToString() + ", 0" + " ครั้ง");
}
}
}
else if (Request.QueryString["dep"] != null)
{
int AllActivity = 0;
List<int> AllAct = new List<int>();
foreach (DataRow Dr in DtName.Rows)
{
int aAct = Activity.AllActivityByOfficeCode(KA.KeyAccountByOfficeCode(Dr["OfficeID"].ToString()), DateTime.Parse(SelectMonth.Text).Month, AD).Rows.Count;
AllAct.Add(aAct);
AllActivity += aAct;
}
if (AllActivity > 0)
{
int i = 0;
foreach (DataRow Dr in DtName.Rows)
{
float Act = (100 * AllAct[i]) / AllActivity;
arrDataValue.Add(Act.ToString());
arrDataTooltip.Add(Dr["OfficeShortName"].ToString() + ", " + AllAct[i].ToString() + " ครั้ง");
i++;
}
}
else
{
foreach (DataRow Dr in DtName.Rows)
{
arrDataValue.Add(Value.ToString());
arrDataTooltip.Add(Dr["OfficeShortName"].ToString() + ", 0" + " ครั้ง");
}
}
}
else
{
int AllActivity = 0;
List<int> AllAct = new List<int>();
foreach (DataRow Dr in DtName.Rows)
{
int aAct = 0;
foreach (DataRow Drr in MyOffice.GetOfficeByRelation(Dr["OfficeID"].ToString(), true).Rows)
{
aAct += Activity.AllActivityByOfficeCode(KA.KeyAccountByOfficeCode(Drr["OfficeID"].ToString()), DateTime.Parse(SelectMonth.Text).Month, AD).Rows.Count;
}
AllAct.Add(aAct);
AllActivity += aAct;
}
if (AllActivity > 0)
{
int i = 0;
foreach (DataRow Dr in DtName.Rows)
{
float Act = (100 * AllAct[i]) / AllActivity;
arrDataValue.Add(Act.ToString());
arrDataTooltip.Add(Dr["OfficeShortName"].ToString() + ", " + AllAct[i].ToString() + " ครั้ง");
i++;
}
}
else
{
foreach (DataRow Dr in DtName.Rows)
{
arrDataValue.Add(Value.ToString());
arrDataTooltip.Add(Dr["OfficeShortName"].ToString() + ", 0" + " ครั้ง");
}
}
}
//Now, we need to convert this data into XML. We convert using StringBuilder concatenation.
StringBuilder xmlData = new StringBuilder();
//Initialize <chart> element
xmlData.AppendFormat("<chart caption='แสดงกิจกรรมการขายในภาพรวมของ {0}' subcaption='เดือน{1} {2}' decimals='0' enableSmartLabels='1' enableRotation='0' bgColor='99CCFF,FFFFFF' bgAlpha='40,100' bgRatio='0,100' bgAngle='360' showBorder='1' startingAngle='70' showPercentValues='1' baseFont='Tahoma' baseFontSize ='12' baseFontColor ='000000'>", OfficeName, DateTime.Parse(SelectMonth.Text).ToString("MMMM"), DateTime.Parse(SelectMonth.Text).ToString("yyyy"));
//Convert data to XML and append
for (int i = 0; i < arrDataName.Count; i++)
{
if (i == 0)
xmlData.AppendFormat("<set label='{0}' value='{1}' tooltext='{2}' isSliced='1' />", arrDataName[i], arrDataValue[i], arrDataTooltip[i]);
else
xmlData.AppendFormat("<set label='{0}' value='{1}' tooltext='{2}' />", arrDataName[i], arrDataValue[i], arrDataTooltip[i]);
}
//Close <chart> element
xmlData.Append("</chart>");
//Create the chart - Column 3D Chart with data contained in xmlData
return FusionCharts.RenderChart("Charts/Pie3D.swf", "", xmlData.ToString(), "productSales", "450", "350", false, false, true);
}
<td align="center"> </td>
</tr>
<tr>
<td><table width="74%" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td width="30%"><h3 align="center">ชื่ออุปกรณ์</h3></td>
<td width="24%"><h3 align="center">จำนวนอุปกรณ์ที่ถือครอง</h3></td>
<td width="46%"><h3 align="center">หน่วยงาน</h3></td>
</tr>
<tr>
<%
while not rs1.eof and rowcount<rs1.pagesize
if bg="tdblue-2" then
bg="tdblue-1"
else
bg="tdblue-2"
end if
%>
<td><div align="center"><%=rs1("Device_Name")%></div></td>
<td><div align="center"><%=rs1("CountDevice_ID")%></div></td>
<td><div align="center"><%=rs1("div_name")%></div></td>
</tr>
<tr>
<td> </td>
<td> </td>
<td> </td>
</tr>
<%
rowcount=rowcount+1
rs1.movenext
wend
%>
</table></td>
</tr>
</table>
<%end if%>
<%
'ส่วนของการแสดงกราฟ
Dim oRs, oRs2, strQuery
'strXML will be used to store the entire XML document generated
Dim strXML
'We also keep a flag to specify whether we've to animate the chart or not.
'If the user is viewing the detailed chart and comes back to this page, he shouldn't
'see the animation again.
Dim animateChart
animateChart = Request.QueryString("animate")
'Set default value of 1
if animateChart="" then
animateChart = "1"
end if
'Create the recordset to retrieve data
Set oRs = Server.CreateObject("ADODB.Recordset")
'Iterate through each factory
'strQuery = "select * from Factory_Master"
strQuery= " SELECT Test_Typedevice.Device_Name, Test_detail.div_name "
strQuery = strQuery+"FROM Test_Typedevice INNER JOIN "
strQuery = strQuery+" Test_Test ON Test_Typedevice.Device_ID = Test_Test.Device_ID AND "
strQuery = strQuery+" Test_Typedevice.div_id = Test_Test.div_id INNER JOIN "
strQuery = strQuery+"Test_detail ON Test_Typedevice.div_id = Test_detail.div_id AND "
strQuery = strQuery+"Test_Test.div_id = Test_detail.div_id "
strQuery = strQuery+"GROUP BY Test_Typedevice.Device_Name, Test_detail.div_name, Test_detail.div_name"
Set oRs = Con.Execute(strQuery)
While Not oRs.Eof
'Now create second recordset to get details for this factory
Set oRs2 = Server.CreateObject("ADODB.Recordset")
'strQuery = "select sum(Quantity) as TotOutput from Factory_Output where FactoryId=" & ors("FactoryId")
strQuery= " SELECT Test_Typedevice.Device_Name, COUNT(Test_Typedevice.Device_ID) AS CountDevice_ID, Test_detail.div_name "
strQuery = strQuery+"FROM Test_Typedevice INNER JOIN "
strQuery = strQuery+" Test_Test ON Test_Typedevice.Device_ID = Test_Test.Device_ID AND "
strQuery = strQuery+" Test_Typedevice.div_id = Test_Test.div_id INNER JOIN "
strQuery = strQuery+"Test_detail ON Test_Typedevice.div_id = Test_detail.div_id AND "
strQuery = strQuery+"Test_Test.div_id = Test_detail.div_id "
strQuery = strQuery+"GROUP BY Test_Typedevice.Device_Name, Test_detail.div_name, Test_detail.div_name"
Set oRs2 = Con.Execute(strQuery)
'Generate <set label='..' value='..' link='..' />
'Note that we're setting link as Detailed.asp?FactoryId=<<FactoryId>>
strXML = strXML & "<set label='" & ors("Device_Name") & "' value='" & ors2("CountDevice_ID") & "' />"
'Close recordset
Set oRs2 = Nothing
oRs.MoveNext
Wend
'Finally, close <chart> element
strXML = strXML & "</chart>"
Set oRs = nothing
'Create the chart - Pie 3D Chart with data from strXML
Call renderChart("App/FusionCharts/Column3D.swf", "", strXML, "FactorySum", 600, 300, false, false)
%>
Load balance : Server 04
