Webフロントエンドjs統計グラフを描画し、データベースにアクセス

10118 ワード

現在、多くのjsライブラリは、JSPサーバ側でJFreeChartコンポーネントなどの画像ストリームを生成してクライアントに渡すことに劣らず、Webクライアントできれいな統計グラフを容易に描くことができます.一般的にhtml 5のcanvas機能をサポートするブラウザが要求され、サポートされていないブラウザと互換性があるため、jsライブラリにはcanvasスクリプトライブラリが別途提供されています.この例では、無料のオープンソースを用いるjqueryベースのjsプロットライブラリであるjqplot、MS ActiveXフレームワーク、MS Acess開発学習例を記録する.ソースの一部:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
	<title>Statistics</title>
	<link type="text/css" rel="stylesheet" href="../css/pagestyle.css"/>
    <script type="text/javascript" src="../js/jquery.min.js"></script>
    <script language="javascript" type="text/javascript" src="../js/excanvas.js"></script>
    
	<script language="javascript" type="text/javascript" src="../js/jquery.jqplot.min.js"></script>
	<script type="text/javascript" src="../js/jqplot.dateAxisRenderer.min.js"></script>
	<link rel="stylesheet" type="text/css" hrf="../css/jquery.jqplot.min.css" />
	
    <script type="text/javascript">
 		
 		var drawline = function(obj,areaid,tt){
		  //var line1=[['2008-08-12 4:00PM',4], ['2008-09-12 4:00PM',6.5], ['2008-10-12 4:00PM',5.7], ['2008-11-12 4:00PM',9], ['2008-12-12 4:00PM',8.2]];
		  var line1 = obj;
		  var plot1 = $.jqplot(areaid, [line1], {
		    title:tt,
		    axes:{
		        xaxis:{
		            renderer:$.jqplot.DateAxisRenderer,
		            tickOptions:{formatString:'%Y/%#m/%#d'}
		        }
		    },
		    series:[{lineWidth:4, markerOptions:{style:'square'}}]
  			});
  		}
    	
		
    	var getData  = function(sql,a1,line){
    		//    
    	//	var a = new Array(3);
    	//	a[0] = new Array(1,2,3);
    	//	a[1] = new Array(3,4,5); 
    	
   			var filefolder = location.href.substring(0,	location.href.indexOf("statistics.html"));   			
   			var dbpath = filefolder.substring(8) + "../db/oilcar.mdb" ; //   "file:///"  ,       			
   			   
   			var con = new ActiveXObject("ADODB.Connection"),
   			 	fso = new ActiveXObject("Scripting.FileSystemObject"),								//①
   			 	connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fso.GetFile(dbpath); // ①       
   			con.Open(connstr);  //①
   			var rs = new ActiveXObject("ADODB.Recordset");   			 			
   			rs.open(sql,con);   			
   			
			while(!rs.eof){
				var theday = rs.Fields.item("date_jiayou").value;
				var carnum =rs.Fields.item("carnumber").value;   		//  	   				
   				var miles = rs.Fields.item("totalmileage").value;		//   ,  
   				var oilleft = rs.Fields.item("oilboxpercent").value;	
   				var price = rs.Fields.item("per_price").value;		//  ,  
   				var oiltype = rs.Fields.item("oiltype").value;	//  
   				var quantity = rs.Fields.item("quantity").value;	// ,  
   				var fee = rs.Fields.item("pay").value;	//   ,  
   				var seller = rs.Fields.item("seller").value; //  
   				var place = rs.Fields.item("location").value;	//  
   				var remark = rs.Fields.item("remark").value;	//     
   				palce = (place=="")?"":place;
   				remark = (remark=="")?"":remark;   				
   				var d = new Date(Date.parse(theday));
   				var y,m,day;
   				y = d.getFullYear();
   				m = d.getMonth()+1 ;
   				day = d.getDate();
   				theday = y +"/" + m +  "/" + day;
   				oilleft = Number(oilleft).toFixed(4);		//     ,  	
   				
   				a1[line] = new Array();
   				a1[line][0] = Date.parse(theday);
   				a1[line][1] = new Number(oilleft);//    new  ,         ,         (111 )  “      ”
   				a1[line][2] = new Number(miles);//    new  ,         ,         (111 )  “      ”
   				a1[line][3] = new Number(price);//    new  ,         ,         (111 )  “      ”
   				a1[line][4] = new Number(quantity);//    new  ,         ,         (111 )  “      ”
   				//a1[line] = new Array(theday,oilleft,miles,price,quantity);
   				//alert(a1[line]);		
   				line++;
   				
   							
				rs.moveNext;
			}   			  			
   			
   			
   			
   			rs.close();  
 			rs = null; 
   			con.Close();
   			con = null;    
   			
    	//	alert(a1[0]);
    		return a1;
    	}
    	
    	
    	var getPrices = function(sql,a2,line){    	
    	
   			var filefolder = location.href.substring(0,	location.href.indexOf("statistics.html"));   			
   			var dbpath = filefolder.substring(8) + "../db/oilcar.mdb" ; //   "file:///"  ,       			
   			   
   			var con = new ActiveXObject("ADODB.Connection"),
   			 	fso = new ActiveXObject("Scripting.FileSystemObject"),								//①
   			 	connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fso.GetFile(dbpath); // ①       
   			con.Open(connstr);  //①
   			var rs = new ActiveXObject("ADODB.Recordset");   			 			
   			rs.open(sql,con);   			
   			
			while(!rs.eof){
				var theday = rs.Fields("date_jiayou");
				var carnum =rs.Fields("carnumber");   		//  	   				
   				var miles = rs.Fields("totalmileage");		//   ,  
   				var oilleft = rs.Fields("oilboxpercent");	
   				var price = rs.Fields("per_price");		//  ,  
   				var oiltype = rs.Fields("oiltype");	//  
   				var quantity = rs.Fields("quantity");	// ,  
   				var fee = rs.Fields("pay");	//   ,  
   				var seller = rs.Fields("seller"); //  
   				var place = rs.Fields("location");	//  
   				var remark = rs.Fields("remark");	//     
   				palce = (place=="")?"":place;
   				remark = (remark=="")?"":remark;   				
   				var d = new Date(Date.parse(theday));
   				var y,m,day;
   				y = d.getFullYear();
   				m = d.getMonth()+1 ;
   				day = d.getDate();
   				theday = y +"/" + m +  "/" + day;
   				oilleft = Number(oilleft).toFixed(4);		//     ,  	
   				
   				a2[line] = new Array();
   				a2[line][0] = new Date(Date.parse(theday));			 	
   				a2[line][1] = new Number(price);//    new  ,         ,         (111 )  “      ”   					
   				line++;     							
				rs.moveNext;
			}   			  			
   			   			
   			rs.close();  
 			rs = null; 
   			con.Close();
   			con = null;       	
    		return a2;
    	}
    
		$(function(){
			
			
			
			 $("#st").click(function(){
			 	 var a1 = new Array();
    			 var line = 0;
			 	 $("#dv_1").html("");
				 var y =   Number($("#t1").val());//    ....
			 	 var d1 = y+"/"+ 1 + "/"+1;
			 	 var d2 = (y+1)+"/"+ 1+ "/"+1;
			 	 //alert(d2);
			 	 var sql = "select * from oilcarlog where date_jiayou>=#"+ d1+"# and  date_jiayou<#"+ d2 +"# order by date_jiayou asc ";
			 	 //var arr = getData(sql);
				 var obj =  getData(sql,a1,line);//     : /   /       /   /   /   
				//alert(obj[1]);
				
				var bar_x = new Array(), bar_y = new Array() ;//x:    y:  
				
				for(i=1;i<obj.length;i++){
					var cur_oilleft = obj[i][1];//        
					var oil_spent = 0 ;//  ,                   
					var cur_miles = obj[i][2] ; //     
					var miles_travel = 0 ;
					var haspoint_calc = false;
					for(j=i-1;j>=0;j--){
						//         1/16 ,          (    )       ,           :        (      )      
						var percent = cur_oilleft - obj[j][1];
						oil_spent += obj[j][4];
						miles_travel = cur_miles - obj[j][2];
						if( percent <=  0.0625  ){							
							//       
							haspoint_calc = true;
							break;
						}						
					}
					if(haspoint_calc){
						var d = new Date(obj[i][0]);		
					//	var year = d.getFullYear() , month = d.getMonth + 1;
										
						var date_calc = new String(d.getFullYear() + "/" +  String(new Number(d.getMonth()) + 1) + "/" + new String(d.getDate()));
						var oil = new Number(100*oil_spent/miles_travel);//       /   
						bar_x.push( date_calc );
						bar_y.push( oil.toFixed(2) ); 
					}
				}
				var obj = new Array();
				for(k=0;k<bar_x.length;k++){
					obj[k] = new Array(String(bar_x[k]),Number(bar_y[k]));
				}

				drawline(obj,'dv_1','      ( /   )');
			});
			
			$("#oil").click(function(){
				var a2 = new Array();
				var line = 0;
				$("#dv_1").html("");
				var y =   Number($("#t1").val());//    ....
			 	var d1 = y+"/"+ 1 + "/"+1;
			 	var d2 = (y+1)+"/"+ 1+ "/"+1;			 	 
			 	var sql = "select * from oilcarlog where date_jiayou>=#"+ d1+"# and  date_jiayou<#"+ d2 +"# order by date_jiayou asc ";
			 	var obj =  getPrices(sql,a2,line);//     : /    /  			 	
			 	drawline(obj,'dv_1','      '); 
			});
	
		});
    </script>
	
</head>

<body>
<div  style="height: 99%;">
<div style="height: 10%;">
	<label style="font-size: 13px; 	font-style: normal;	font-weight: bold;">    :</label>
	<input type="text" id="t1" name="theday" value="2015" /><span style="font-size: 10px">( :2015)</span>
	<input  type="button" value="  "  class="btn_statistics" id="st" />|<input  type="button" value="    "  class="btn_statistics" id="oil" />
</div>
<div style="height: 90%;">
<fieldset style="font-size: 14px; color:blue; width: 90%; height:90% ">
<legend>    </legend>
	<div id="dv_1" style="width:600px; height:350px; margin-top:20px; margin-left:20px;">	
	</div>
</fieldset>
</div>
</div>
</body>
</html>

リンク:http://www.jqplot.com/