<%
	/*We've included ../Includes/FusionCharts.jsp, which contains functions
	to help us easily create the charts.*/
%>
<%@ include file="../Includes/FusionCharts.jsp"%>
<%@ include file="../Includes/DBConn.jsp"%>

<%@ page import="java.sql.Statement"%>
<%@ page import="java.sql.ResultSet"%>
<%@ page import="java.sql.Date"%>

<%@ page import="java.text.SimpleDateFormat"%>
<HTML>
	<HEAD>
		<TITLE>FusionCharts - Database + JavaScript Example</TITLE>
		<%
			/*
			In this example, we show a combination of database + JavaScript rendering using FusionCharts.
			
			 The entire app (page) can be summarized as under. This app shows the break-down
			 of factory wise output generated. In a pie chart, we first show the sum of quantity
			 generated by each factory. These pie slices, when clicked would show detailed date-wise
			 output of that factory.
			
			 The XML data for the pie chart is fully created in JSP at run-time. jsp interacts
			 with the database and creates the XML for this.
			 Now, for the column chart (date-wise output report), we do not submit request to the server.
			 Instead we store the data for the factories in JavaScript arrays. These JavaScript
			 arrays are rendered by our JSP Code (at run-time). We also have a few defined JavaScript
			 functions which react to the click event of pie slice.
			
			 We' ve used an Access database which is present in ../DB/FactoryDB.mdb. 
			 It just contains two tables, which are linked to each other. 
			
			 Before the page is rendered, we need to connect to the database and get the
			 data, as we'll need to convert this data into JavaScript variables.
			*/
			/* The following string will contain the JS Data and variables.
			 This string will be built in JSP and rendered at run-time as JavaScript.
			 */
			 
			String jsVarString = "";
			
			//Database Objects
			Statement st1=null,st2=null;
			ResultSet rs1=null,rs2=null;
		
			String strQuery="";
		
			int indexCount = -1;
			
			//Create the recordset to retrieve data
			//We need to create a Scrollable ResultSet so that we can reuse it for creating the chart later
			st1=oConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
		
			//Create the query
			strQuery = "select * from Factory_Master";
			rs1 = st1.executeQuery(strQuery);
		
			String factoryId=null;
			String factoryName=null;
		
			String quantity="";
			java.sql.Date date=null;
			java.util.Date uDate=null;
			String uDateStr="";
			while(rs1.next()) {
				indexCount += 1;
				factoryId=rs1.getString("FactoryId");
				factoryName=rs1.getString("FactoryName");
				
				//Create JavaScript code to add sub-array to data array
				//data is an array defined in JavaScript (see below)
				//We've added \t & \n to data so that if you View Source of the
				//output HTML, it will appear properly. It helps during debugging
				jsVarString += "\t\t"+ "data[" + indexCount + "] = new Array();\n" ; 
				
				//Now create second recordset to get date-wise details for this factory
				
				strQuery = "select * from Factory_Output where FactoryId=" +factoryId+ " order by DatePro Asc "; 			
				st2=oConn.createStatement();
				rs2 = st2.executeQuery(strQuery);
				
				while(rs2.next()){
				date=rs2.getDate("DatePro");
				quantity=rs2.getString("Quantity");
				if(date!=null) {
				  uDate=new java.util.Date(date.getTime());
				  SimpleDateFormat sdf=new SimpleDateFormat("dd/MM");
				  uDateStr=sdf.format(uDate);
				}
				//Put this data into JavaScript as another nested array.
				//Finally the array would look like data[factoryIndex][i][dataLabel,dataValue]
				jsVarString +="\t\t"+"data[" + indexCount + "].push(new Array('" + uDateStr + "'," +quantity+"));" +"\n\r";
				}
				try {
					if(null!=rs2){
						rs2.close();
						rs2=null;
					}
				}catch(java.sql.SQLException e){
				 	System.out.println("Could not close the resultset");
				}
				try{
					if(null!=st2) {
						st2.close();
						st2=null;
					}
				}catch(java.sql.SQLException e){
				 	System.out.println("Could not close the statement");
				}
			}
			
			
			%>
		<SCRIPT LANGUAGE="Javascript" SRC="../../FusionCharts/FusionCharts.js">
				//You need to include the above 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>
		
		<SCRIPT LANGUAGE="JavaScript">
				//Here, we use a mix of server side code (jsp) and JavaScript to
				//render our data for factory chart in JavaScript variables. We'll later
				//utilize this data to dynamically plot charts.
				
				//All our data is stored in the data array. From jsp, we iterate through
				//each resultset of data and then store it as nested arrays in this data array.
				var data = new Array();
				
				<%
				/*Write the data as JavaScript variables here
				The data is now present as arrays in JavaScript. Local JavaScript functions
				can access it and make use of it. We'll see how to make use of it.
				*/
				%>
				<%=jsVarString%>
				/** 
				 * updateChart method is invoked when the user clicks on a pie slice.
				 * In this method, we get the index of the factory, build the XML data
				 * for that that factory, using data stored in data array, and finally
				 * update the Column Chart.
				 * @param	factoryIndex	Sequential Index of the factory.
				 * @param	factoryName For display purpose
				*/		
				function updateChart(factoryIndex,factoryName){
					//Storage for XML data document
					var strXML = "<chart palette='2' caption='Factory: " + factoryName  + " Output ' subcaption='(In Units)' xAxisName='Date' showValues='1' labelStep='2' >";
					
					//Add <set> elements
					var i=0;
					for (i=0; i<data[factoryIndex].length; i++){
						strXML = strXML + "<set label='" + data[factoryIndex][i][0] + "' value='" + data[factoryIndex][i][1] + "' />";
					}
					
					//Closing Chart Element
					strXML = strXML + "</chart>";
								
					//Get reference to chart object using Dom ID "FactoryDetailed"
					var chartObj = getChartFromId("FactoryDetailed");
					//Update it's XML
					chartObj.setDataXML(strXML);
				}
			</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>
		<BODY>
			<CENTER>
			<h2>FusionCharts Database + JavaScript Example</h2>
			<h4>Inter-connected charts - Click on any pie slice to see detailed
			chart below.</h4>
			<p>The charts in this page have been dynamically generated using
			data contained in a database. We've NOT hard-coded the data in
			JavaScript.</p>
			<%		
				//Initialize the Pie chart with sum of production for each of the factories
				//strXML will be used to store the entire XML document generated
				String strXML ="";
				String totalOutput="";
				
				//Re-initialize Index
				indexCount=-1;
				
				//Generate the chart element
				strXML = "<chart caption='Factory Output report' subCaption='By Quantity' pieSliceDepth='30' showBorder='1' formatNumberScale='0' numberSuffix=' Units' >";
				
				//Move back to first index of the factory master recordset
				rs1.beforeFirst();
				
				while(rs1.next()){
				//Update index count - sequential
					indexCount = indexCount + 1;
			
					factoryId=rs1.getString("FactoryId");
					factoryName=rs1.getString("FactoryName");
					
					//Now create second recordset to get details for this factory
					
					strQuery = "select sum(Quantity) as TotOutput from Factory_Output where FactoryId=" +factoryId;
					st2=oConn.createStatement();
					rs2 = st2.executeQuery(strQuery);
					if(rs2.next()){
						totalOutput=rs2.getString("TotOutput");
					}				
					//Generate <set label='..' value='..' link='..' />
					//Note that we're setting link as updateChart(factoryIndex,factoryName) - JS Function
					strXML += "<set label='" +factoryName+ "' value='" +totalOutput+"' link='javaScript:updateChart("+indexCount + ",&quot;"+factoryName+"&quot;)'/>";
					try {
						if(null!=rs2){
							rs2.close();
							rs2=null;
						}
					}catch(java.sql.SQLException e){
					 	System.out.println("Could not close the resultset");
					}
					try{
						if(null!=st2) {
							st2.close();
							st2=null;
						}
					}catch(java.sql.SQLException e){
					 	System.out.println("Could not close the statement");
					}
				}
				//Finally, close <chart> element
				strXML += "</chart>";
				//close the resultset,statement,connection
				//enclose them in try catch block
				try {
					if(null!=rs1){
						rs1.close();
						rs1=null;
					}
				}catch(java.sql.SQLException e){
					 //do something
					 System.out.println("Could not close the resultset");
				}	
				try {
					if(null!=st1) {
						st1.close();
						st1=null;
					}
				    }catch(java.sql.SQLException e){
					 	System.out.println("Could not close the statement");
					}
				try {
					if(null!=oConn) {
					    oConn.close();
					    oConn=null;
					}
				    }catch(java.sql.SQLException e){
					 	System.out.println("Could not close the connection");
					}
						
				//Create the chart - Pie 3D Chart with data from strXML
				String chartCode= createChart("../../FusionCharts/Pie3D.swf", "", strXML, "FactorySum", 500, 250, false, false);
			%>
			<%=chartCode%> 
			<BR>
			<%
				//Column 2D Chart with changed "No data to display" message
				//We initialize the chart with <chart></chart>
				String chartCode2= createChart("../../FusionCharts/Column2D.swf?ChartNoDataText=Please select a factory from pie chart above to view detailed data.", "", "<chart></chart>", "FactoryDetailed", 600, 250, false, false);
			%>
			<%=chartCode2%>
			<BR>
			<BR>
			<a href='../NoChart.html' target="_blank">Unable to see the charts above?</a></CENTER>
	</BODY>
</HTML>
