今天就来聊聊go里面如何绘制echarts图,

需求:使用go来生成自动化巡检报告,数据库的性能采集也是通过go来实现的,这里就不多说,那如何能够直观的展示监控数据呢,当然前面我有写过flask +python+vue 的监控系统:https://blog.csdn.net/shiyu1157758655/article/details/103278459

今天我要做的是,不通过监控系统,直接把想要的数据展示出来,生成html文件,这里主要就是介绍golang 中 go-echarts 如何使用 ,闲话少说,直接上图(部分图)

 

 

 

 

那上面的是如何实现的呢,直接上代码

package main

import (
	"database/sql"
	"fmt"
	"github.com/go-echarts/go-echarts/charts"
	_ "github.com/go-sql-driver/mysql"
	"log"
	"os"
	"strconv"
	"strings"
	"time"
)
const (

	mysqlHost ="xxxx"           //mysql主机ip
	mysqlPort="xxx"                     //mysql端口
	mysqlUser="xxx"                 //mysql登录账号
	mysqlPassword="xxx"             //mysql登录账号密码
	mysqlDbname="xxx"               //mysql数据库名字
	mysqlCharset="utf8"                  //mysql字符集
)



func  checkErr(info string,err error){
	if  err  != nil{
		nowTime := time.Now().Format("2006-01-02 15:04:05")
		msg := fmt.Sprintf("mysql is erro:%v !", err)
		msgError := fmt.Sprintf("Problem!! %s %s %s", info, msg, nowTime)
		//sendMess(msgError)
		log.Println(msgError)
	}
}

func Query(db *sql.DB,dbInfo string,query string)(result map[int]map[string]string) {
	rows,err := db.Query(query)
	checkErr(dbInfo,err)
	defer rows.Close()
	cols,_ :=rows.Columns()  //返回所有列
	vals :=make([][]byte,len(cols)) //这里表示一行所有列的值,用[]byte表示
	scans :=make([]interface{},len(cols)) //这里表示一行填充数据
	for k,_:=range vals{
		scans[k] = &vals[k]
	}
	i :=0

	result = make(map[int]map[string]string)
	for rows.Next(){
		rows.Scan(scans...)  //填充数据
		row :=make(map[string]string) //每行数据
		for k,v := range vals {   //把vals中的数据复制到row中
			key :=cols[k]
			//fmt.Println(string(v)) //这里把[]byte数据转成string
			row[key] = string(v)

		}
		result[i] = row
		i++
	}
	rows.Close()

	return result
}

func stringToInt(s string)int{
	toInit,err :=strconv.Atoi(s)
	checkErr("dbInfo",err)
	return toInit

}
func stringToInt64(s string) int64{
	toInit64,err  :=strconv.ParseInt(s, 10, 64)
	checkErr("dbInfo",err)
	return toInit64
}


func main() {
	dbInfo := fmt.Sprintf("%s:%s", mysqlHost, mysqlPort) //system/oracle@10.0.1.60:1521/pig

	mysqlDsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=%s", mysqlUser, mysqlPassword, mysqlHost, mysqlPort, mysqlDbname, mysqlCharset)
	//log.Println(dbDsn)
	//打开连接

	db, err := sql.Open("mysql", mysqlDsn)
	checkErr(dbInfo, err)
	ping := db.Ping()
	checkErr(dbInfo, ping)
	//lineBar:=charts.NewKLine()
	//lineBar.SetGlobalOptions(charts.TitleOpts{Title:"当前连接数"})
	dbSql:="select * from (select distinct f_id,f_ip,f_class,f_port,f_role,f_version,f_startup_time,\"oracle\" f_type from t_oracle_instances order by f_class)a union all select * from (select distinct f_id,f_ip,f_class,f_port,f_role,f_version,f_startup_time,\"mysql\" f_type from t_mysql_instances order by f_class,f_role desc) b;"
	dbList := Query(db, dbInfo, dbSql)
	p1 := charts.NewPage()
	//p2:=charts.NewPage()
	nowTime, _ := time.Parse("2006-01-02 15:04:05", time.Now().Format("2006-01-02 15:04:05"))
	endTime := time.Now()
	startTime := endTime.AddDate(0, 0, -1)
	endUinxTime := endTime.Unix()
	startUnixTime := startTime.Unix()
	for i := 0; i < len(dbList); i++ {

		if dbList[i]["f_type"] == "oracle" {

			runTime, _ := time.Parse("2006-01-02 15:04:05", dbList[i]["f_startup_time"])
			diffTime := nowTime.Sub(runTime)
			diffDay := fmt.Sprintf("%v天", int(diffTime.Hours()/24))


			oracleSql := fmt.Sprintf("select f_check_time ,f_database_role,f_archivelog,f_db_size,f_db_file_size,f_session_total,f_session_actives,f_session_waits,f_processes,f_dg_stats,f_dg_delay,f_buffer_hit,f_library_hit,f_latch_hit,f_in_memory_sort_hit,f_buffer_nowait_hit,f_redo_nowait_ratio,f_sga_pct,f_pga_pct,f_db_block_gets,f_consistent_gets,f_db_block_changes,f_redo_writes,f_physical_reads_direct,f_physical_writes_direct,f_opened_cursors_current,f_user_commits,f_user_rollbacks from t_oracle_monitor_status where f_instances_id=%v and f_check_time BETWEEN %v and %v", dbList[i]["f_id"], startUnixTime, endUinxTime)
			result := Query(db, dbInfo, oracleSql)

			var xTime []string
			var dbSize []string
			var dbFileSize []string
			var sessionTotal []string
			var sessionActives []string
			var sessionWaits []string
			var processes []string
			var bufferHit []string

			var sgaPct []string
			var pgaPct []string
			var userCommit []string
			var userRollback []string
			var timeDiff int
			var dgDelay []string
			if len(result) <0 {
				for j := 0; j < len(result)-1; j++ {
					if dbList[i]["f_role"] == "0" {
						if result[j+1]["f_dg_delay"] == "-1" {
							dgDelay = append(dgDelay, "null")
						} else {
							dgDelay = append(dgDelay, result[j+1]["f_dg_delay"])
						}
					}
					timeDiff = stringToInt(result[j+1]["f_check_time"]) - stringToInt(result[j]["f_check_time"])
					if result[j+1]["f_db_size"] == "-1" {
						dbSize = append(dbSize, "null")
					} else {
						dbSize = append(dbSize, result[j+1]["f_db_size"])
					}
					if result[j+1]["f_db_file_size"] == "-1" {
						dbFileSize = append(dbFileSize, "null")
					} else {
						dbFileSize = append(dbFileSize, result[j+1]["f_db_file_size"])
					}
					if result[j+1]["f_session_total"] == "-1" {
						sessionTotal = append(sessionTotal, "null")
					} else {
						sessionTotal = append(sessionTotal, result[j+1]["f_session_total"])
					}
					if result[j+1]["f_session_actives"] == "-1" {
						sessionActives = append(sessionActives, "null")
					} else {
						sessionActives = append(sessionActives, result[j+1]["f_session_actives"])
					}
					if result[j+1]["f_session_waits"] == "-1" {
						sessionWaits = append(sessionWaits, "null")
					} else {
						sessionWaits = append(sessionWaits, result[j+1]["f_session_waits"])
					}
					if result[j+1]["f_processes"] == "-1" {
						processes = append(processes, "null")
					} else {
						processes = append(processes, result[j+1]["f_processes"])
					}

					if result[j+1]["f_buffer_hit"] == "-1" {
						bufferHit = append(bufferHit, "null")
					} else {
						bufferHit = append(bufferHit, result[j+1]["f_buffer_hit"])
					}

					if result[j+1]["f_sga_pct"] == "-1" {
						sgaPct = append(sgaPct, "null")
					} else {
						sgaPct = append(sgaPct, result[j+1]["f_sga_pct"])
					}
					if result[j+1]["f_pga_pct"] == "-1" {
						pgaPct = append(pgaPct, "null")
					} else {
						pgaPct = append(pgaPct, result[j+1]["f_pga_pct"])
					}
					if result[j+1]["f_user_commits"] == "-1" || result[j]["f_user_commits"] == "-1" {
						userCommit = append(userCommit, "null")
					} else {
						commitValue, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", (float64(stringToInt(result[j+1]["f_user_commits"]))-float64(stringToInt(result[j]["f_user_commits"])))/float64(timeDiff)), 64)
						userCommit = append(userCommit, fmt.Sprintf("%v", commitValue))
					}
					if result[j+1]["f_user_rollbacks"] == "-1" || result[j]["f_user_rollbacks"] == "-1" {
						userRollback = append(userRollback, "null")
					} else {
						rollbackValue, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", (float64(stringToInt(result[j+1]["f_user_rollbacks"]))-float64(stringToInt(result[j]["f_user_rollbacks"])))/float64(timeDiff)), 64)
						userRollback = append(userRollback, fmt.Sprintf("%v", rollbackValue))
					}

					xTime = append(xTime, time.Unix(stringToInt64(result[j]["f_check_time"]), 0).Format("2006-01-02 15:04:05"))

				}
				//var hostInfo[] string
				var isRole []string
				if dbList[i]["f_role"] == "1" {
					isRole = append(isRole, "主库")
				} else {
					isRole = append(isRole, "从库")
				}
				hostJs := "<table><tr><th>类型</th><th>产品线</th><th>主机</th><th>端口</th><th>版本</th><th>主从</th><th>运行时间</th><th>开始时间</th><th>结束时间</th></tr>"
				hostJs += fmt.Sprintf("<tr><td>%v</td><td>%v</td><td>%v</td><td>%v</td><td>%v</td><td>%v</td><td>%v</td><td>%v</td><td>%v</td></tr>",
					"oracle", dbList[i]["f_class"], dbList[i]["f_ip"], dbList[i]["f_port"], dbList[i]["f_version"], isRole[0], diffDay, startTime.Format("2006-01-02 15:04:05"), endTime.Format("2006-01-02 15:04:05"))
				hostJs += "</table>"

				hostBar := charts.NewBar()
				//echarts=append(echarts, hostBar)
				//log.Println(hostBar)
				//log.Println(echarts)
				hostBar.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
				//hostBar.Width = "100%"
				hostBar.Height = "100px"
				hostBarChartID := fmt.Sprintf("%v_%v_%v_serverInfo", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
				hostBar.ChartID = hostBarChartID

				//js:=fmt.Sprintf("let id = document.getElementById('%v');    let code=';\n let data=%v;for(let i=0;i!=data.length;i++){\n         if (i==0){\n         code+='<tr><td>'+data[i]+'</td>';\n         }else if(i==data.length-1){\n             code+='<td>'+data[i]+'</td></tr>';\n         }else{\n             code+='<td>'+data[i]+'</td>';\n         }\n     }\n     id.innerHTML=code+'<table>';",hostBarChartID,hostInfo)
				hostBar.AddJSFuncs(fmt.Sprintf("document.getElementById('%v').innerHTML='%v';", hostBarChartID, hostJs))

				//sessionLineBar := charts.NewLine()

				sessionLineBar := charts.NewLine()
				sessionLineBar.SetGlobalOptions(charts.TitleOpts{Title: "连接数(单位:个)"})
				sessionLineBar.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
				sessionLineBar.ChartID = fmt.Sprintf("%v_%v_%v_sessionBar", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
				sessionLineBar.TextStyle.FontSize = 18
				sessionLineBar.Height = "300px"
				//sessionLineBar.Width = "100%"
				sessionLineBar.SetGlobalOptions(charts.ToolboxOpts{Show: true})

				var markPoints = []charts.SeriesOptser{
					charts.MPNameTypeItem{Name: "最大值", Type: "max"},
					charts.MPNameTypeItem{Name: "平均值", Type: "average"},
					charts.MPNameTypeItem{Name: "最小值", Type: "min"},
					charts.MPStyleOpts{Label: charts.LabelTextOpts{Show: true}},
				}
				sessionLineBar.AddXAxis(xTime).
					AddYAxis("会话总数", sessionTotal, markPoints...).
					AddYAxis("会话活动数", sessionActives, markPoints...).
					AddYAxis("等待会话数", sessionWaits, markPoints...)

				//processLineBar := charts.NewLine()
				processLineBar := charts.NewLine()
				processLineBar.SetGlobalOptions(charts.TitleOpts{Title: "进程数(单位:个)"})
				processLineBar.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
				processLineBar.ChartID = fmt.Sprintf("%v_%v_%v_processBar", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
				processLineBar.Height = "300px"
				processLineBar.TextStyle.FontSize = 18
				//processLineBar.Width = "100%"
				processLineBar.SetGlobalOptions(charts.ToolboxOpts{Show: true})
				processLineBar.AddXAxis(xTime).AddYAxis("进程数", processes, markPoints...)

				//主从延迟
				dgDelayLineBar := charts.NewLine()

				dgDelayLineBar.SetGlobalOptions(charts.TitleOpts{Title: "延迟时间(单位:秒)"})
				dgDelayLineBar.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
				dgDelayLineBar.ChartID = fmt.Sprintf("%v_%v_%v_dgdelayBar", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
				dgDelayLineBar.Height = "300px"
				//dgDelayLineBar.Width = "100%"
				dgDelayLineBar.TextStyle.FontSize = 18
				dgDelayLineBar.SetGlobalOptions(charts.ToolboxOpts{Show: true})
				dgDelayLineBar.AddXAxis(xTime).AddYAxis("延迟时间", dgDelay, markPoints...)

				//dbSizeLineBar := charts.NewLine()
				dbSizeLineBar := charts.NewLine()
				dbSizeLineBar.SetGlobalOptions(charts.TitleOpts{Title: "数据库大小(单位:G)"})
				dbSizeLineBar.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})

				dbSizeLineBar.ChartID = fmt.Sprintf("%v_%v_%v_dbsizeBar", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])

				dbSizeLineBar.Height = "300px"
				dbSizeLineBar.TextStyle.FontSize = 18
				//dbSizeLineBar.Width = "100%"
				dbSizeLineBar.SetGlobalOptions(charts.ToolboxOpts{Show: true})
				dbSizeLineBar.AddXAxis(xTime).
					AddYAxis("数据库大小", dbSize, markPoints...).
					AddYAxis("数据库文件大小", dbFileSize, markPoints...)

				tablespaceSql := fmt.Sprintf("select * from t_oracle_tablespace where f_instances_id=%v and f_check_time=(select max(f_check_time) from t_oracle_tablespace where f_instances_id=%v) group by f_tablespace_name order by f_used_rate desc", dbList[i]["f_id"], dbList[i]["f_id"])

				tablespaceResult := Query(db, dbInfo, tablespaceSql)
				var tablespaceName []string
				var tablespaceUsedRate []string

				//var Results [][] string

				tablespaceTable := "<table ><tr><th>实例名</th><th>主机</th><th>端口</th><th>表空间名称</th><th>总大小(M)</th><th>已用空间(M)</th><th>可用空间(M)</th><th>使用率(%)</th><th>监控时间</th></tr>"
				for k := 0; k < len(tablespaceResult); k++ {
					//var Result [] string
					//Result=append(Result,fmt.Sprintf("'%v','%v','%v','%v','%vM','%vM','%vM','%v%%','%v'",
					//dbList[i]["f_class"],dbList[i]["f_ip"],dbList[i]["f_port"],tablespaceResult[k]["f_tablespace_name"],
					//tablespaceResult[k]["f_max_size"],tablespaceResult[k]["f_used"],
					//stringToInt(tablespaceResult[k]["f_max_size"])-stringToInt(tablespaceResult[k]["f_used"]),
					//tablespaceResult[k]["f_used_rate"],
					//	time.Unix(stringToInt64(tablespaceResult[k]["f_check_time"]), 0).Format("2006-01-02 15:04:05")))
					//Results=append(Results,Result)
					tablespaceTable += fmt.Sprintf("<tr><td>%v</td><td>%v</td><td>%v</td><td>%v</td><td>%vM</td><td>%vM</td><td>%vM</td><td>%v%%</td><td>%v</td></tr>",
						dbList[i]["f_class"], dbList[i]["f_ip"], dbList[i]["f_port"], tablespaceResult[k]["f_tablespace_name"], tablespaceResult[k]["f_max_size"],
						tablespaceResult[k]["f_used"], stringToInt(tablespaceResult[k]["f_max_size"])-stringToInt(tablespaceResult[k]["f_used"]),
						tablespaceResult[k]["f_used_rate"], time.Unix(stringToInt64(tablespaceResult[k]["f_check_time"]), 0).Format("2006-01-02 15:04:05"))

					tablespaceName = append(tablespaceName, tablespaceResult[k]["f_tablespace_name"])
					tablespaceUsedRate = append(tablespaceUsedRate, tablespaceResult[k]["f_used_rate"])
				}
				tablespaceTable += "</table>"

				//展示tablespace 详细信息
				//tablespaceBar := charts.NewBar()
				tablespaceBar := charts.NewBar()
				tablespaceBar.SetGlobalOptions(charts.TitleOpts{Title: "表空间使用率(单位:%)"})
				tablespaceBar.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
				tablespaceBar.ChartID = fmt.Sprintf("%v_%v_%v_tablespaceBar", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
				tablespaceBar.Height = "300px"
				tablespaceBar.TextStyle.FontSize = 18
				//tablespaceBar.Width = "100%"
				tablespaceBar.SetGlobalOptions(charts.ToolboxOpts{Show: true})
				//bar.AddXAxis(nameItems).AddYAxis("商家A",[]int{20,30,40,10,24,60}).AddYAxis("商家B",[]int{35,14,25,60,44,23})
				tablespaceBar.AddXAxis(tablespaceName).AddYAxis("表空间使用率", tablespaceUsedRate)
				//tablespaceBar.SetGlobalOptions(charts.YAxisOpts{Type:"value",AxisLabel: charts.LabelTextOpts{Formatter: "{value}"}})
				//tablespaceBar.SetGlobalOptions(charts.GlobalOptser())
				//LabelTextOpts{Show:true,Color: "blue",Position: "top",Formatter: "%"}
				tablespaceBar.SetSeriesOptions(charts.LabelTextOpts{Show: true, Position: "top", Formatter: "{c}" + "%"})

				//tablespaceInfo:=charts.NewBar()
				tablespaceInfo := charts.NewBar()
				tablespaceInfo.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
				//tablespaceInfo.Width = "100%"
				tablespaceInfo.Height = "400px"
				tablespaceChartID := fmt.Sprintf("%v_%v_%v_tablespaceInfo", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
				tablespaceInfo.ChartID = tablespaceChartID
				tablespaceJs := fmt.Sprintf("document.getElementById('%v').innerHTML='%v';",
					tablespaceChartID, tablespaceTable)
				tablespaceInfo.AddJSFuncs(tablespaceJs)

				sgaPgaLineBar := charts.NewLine()

				sgaPgaLineBar.SetGlobalOptions(charts.TitleOpts{Title: "sga/pga使用率(单位:%)"})
				sgaPgaLineBar.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
				sgaPgaLineBar.ChartID = fmt.Sprintf("%v_%v_%v_sgapgaBar", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])

				sgaPgaLineBar.Height = "300px"
				//sgaPgaLineBar.Width = "100%"
				sgaPgaLineBar.SetGlobalOptions(charts.ToolboxOpts{Show: true})
				sgaPgaLineBar.TextStyle.FontSize = 18
				sgaPgaLineBar.AddXAxis(xTime).
					AddYAxis("sga", sgaPct, markPoints...).
					AddYAxis("pga", pgaPct, markPoints...)

				bufferHitLineBar := charts.NewLine()
				bufferHitLineBar.SetGlobalOptions(charts.TitleOpts{Title: "buffer hit(命中率)(单位:%)"})
				bufferHitLineBar.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
				bufferHitLineBar.ChartID = fmt.Sprintf("%v_%v_%v_bufferhitBar", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
				bufferHitLineBar.Height = "300px"
				bufferHitLineBar.TextStyle.FontSize = 18
				//bufferHitLineBar.Width = "100%"
				bufferHitLineBar.SetGlobalOptions(charts.ToolboxOpts{Show: true})

				bufferHitLineBar.AddXAxis(xTime).
					AddYAxis("buff_hit", bufferHit, markPoints...)

				transactionLineBar := charts.NewLine()
				transactionLineBar.SetGlobalOptions(charts.TitleOpts{Title: "Transactions(单位:次/s)"})
				transactionLineBar.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
				transactionLineBar.ChartID = fmt.Sprintf("%v_%v_%v_transactionBar", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
				transactionLineBar.Height = "300px"
				//transactionLineBar.Width = "100%"
				transactionLineBar.TextStyle.FontSize = 18
				transactionLineBar.SetGlobalOptions(charts.ToolboxOpts{Show: true})

				transactionLineBar.AddXAxis(xTime).
					AddYAxis("user_commits", userCommit, markPoints...).
					AddYAxis("user_rollbacks", userRollback, markPoints...)
				p1.Add(
					hostBar,
					sessionLineBar,
					processLineBar,
				)
				if dbList[i]["f_role"] == "0" {
					p1.Add(dgDelayLineBar)
				}
				p1.Add(
					dbSizeLineBar,
					tablespaceBar,
					tablespaceInfo,
					sgaPgaLineBar,
					bufferHitLineBar,
					transactionLineBar,
				)
				//log.Println(p)

			}
		}

		if dbList[i]["f_type"] == "mysql"{
			//nowTime,_:=time.Parse("2006-01-02 15:04:05", time.Now().Format("2006-01-02 15:04:05"))
			//runTime,_:=time.Parse("2006-01-02 15:04:05",dbList[i]["f_startup_time"])
			//diffTime:=nowTime.Sub(runTime)
			//diffDay:=fmt.Sprintf("%v天",int(diffTime.Hours()/24))
			diffDay:=dbList[i]["f_startup_time"]+"天"

			mysqlSql := fmt.Sprintf("select *  from t_mysql_monitor_status where f_instances_id=%v and f_check_time BETWEEN %v and %v", dbList[i]["f_id"],startUnixTime,endUinxTime)
			result := Query(db, dbInfo, mysqlSql)

			var xTime []string
			var dgDelay [] string
			var threadsRunning [] string
			var threadsConnecteds  [] string
			var threadsCached [] string
			var comInsert [] string
			var comUpdate [] string
			var comDelete [] string
			var comSelect [] string
			var qps [] string
			var tps [] string
			var slowQueries [] string
			var bufferPoolHit [] string
			var bytesReceived [] string
			var bytesSent [] string
			var timeDiff int
			if len(result) >0 {
				for j := 0; j < len(result)-1; j++ {
					xTime = append(xTime, time.Unix(stringToInt64(result[j]["f_check_time"]), 0).Format("2006-01-02 15:04:05"))
					if dbList[i]["f_role"] == "0" {
						if result[j+1]["f_delay"] == "-1" {
							dgDelay = append(dgDelay, "null")
						} else {
							dgDelay = append(dgDelay, result[j+1]["f_delay"])
						}
					}
					timeDiff = stringToInt(result[j+1]["f_check_time"]) - stringToInt(result[j]["f_check_time"])

					if result[j+1]["f_threads_running"] == "-1" {
						threadsRunning = append(threadsRunning, "null")
					} else {
						threadsRunning = append(threadsRunning, result[j+1]["f_threads_running"])
					}

					if result[j+1]["f_threads_connected"] == "-1" {
						threadsConnecteds = append(threadsConnecteds, "null")
					} else {
						threadsConnecteds = append(threadsConnecteds, result[j+1]["f_threads_connected"])
					}

					if result[j+1]["f_threads_cached"] == "-1" {
						threadsCached = append(threadsCached, "null")
					} else {
						threadsCached = append(threadsCached, result[j+1]["f_threads_cached"])
					}

					if result[j+1]["f_com_insert"] == "-1" || result[j]["f_com_insert"] == "-1" {
						comInsert = append(comInsert, "null")
					} else {
						comInsertValue, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", (float64(stringToInt(result[j+1]["f_com_insert"]))-float64(stringToInt(result[j]["f_com_insert"])))/float64(timeDiff)), 64)
						comInsert = append(comInsert, fmt.Sprintf("%v", comInsertValue))
					}

					if result[j+1]["f_com_update"] == "-1" || result[j]["f_com_update"] == "-1" {
						comUpdate = append(comUpdate, "null")
					} else {
						comUpdateValue, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", (float64(stringToInt(result[j+1]["f_com_update"]))-float64(stringToInt(result[j]["f_com_update"])))/float64(timeDiff)), 64)
						comUpdate = append(comUpdate, fmt.Sprintf("%v", comUpdateValue))
					}

					if result[j+1]["f_com_delete"] == "-1" || result[j]["f_com_delete"] == "-1" {
						comDelete = append(comDelete, "null")
					} else {
						comDeleteValue, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", (float64(stringToInt(result[j+1]["f_com_delete"]))-float64(stringToInt(result[j]["f_com_delete"])))/float64(timeDiff)), 64)
						comDelete = append(comDelete, fmt.Sprintf("%v", comDeleteValue))
					}

					if result[j+1]["f_com_select"] == "-1" || result[j]["f_com_select"] == "-1" {
						comSelect = append(comSelect, "null")
					} else {
						comSelectValue, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", (float64(stringToInt(result[j+1]["f_com_select"]))-float64(stringToInt(result[j]["f_com_select"])))/float64(timeDiff)), 64)
						comSelect = append(comSelect, fmt.Sprintf("%v", comSelectValue))
					}

					if result[j+1]["f_queries"] == "-1" || result[j]["f_queries"] == "-1" {
						qps = append(qps, "null")
					} else {
						qpsValue, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", (float64(stringToInt(result[j+1]["f_queries"]))-float64(stringToInt(result[j]["f_queries"])))/float64(timeDiff)), 64)
						qps = append(qps, fmt.Sprintf("%v", qpsValue))
					}

					if result[j+1]["f_com_commit"] == "-1" || result[j]["f_com_commit"] == "-1" || result[j+1]["f_com_rollback"] == "-1" || result[j]["f_com_rollback"] == "-1" {
						tps = append(tps, "null")
					} else {
						tpsValue, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", ((float64(stringToInt(result[j+1]["f_com_commit"]))-float64(stringToInt(result[j]["f_com_commit"])))+(float64(stringToInt(result[j+1]["f_com_rollback"]))-float64(stringToInt(result[j]["f_com_rollback"]))))/float64(timeDiff)), 64)
						tps = append(tps, fmt.Sprintf("%v", tpsValue))
					}

					if result[j+1]["f_slow_queries"] == "-1" || result[j]["f_slow_queries"] == "-1" {
						slowQueries = append(slowQueries, "null")
					} else {
						slowQueriesValue, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", (float64(stringToInt(result[j+1]["f_slow_queries"]))-float64(stringToInt(result[j]["f_slow_queries"])))/float64(timeDiff)), 64)
						slowQueries = append(slowQueries, fmt.Sprintf("%v", slowQueriesValue))
					}

					if result[j+1]["f_buffer_pool_hit"] == "-1" {
						bufferPoolHit = append(bufferPoolHit, "null")
					} else {
						bufferPoolHit = append(bufferPoolHit, result[j+1]["f_buffer_pool_hit"])
					}

					if result[j+1]["f_bytes_received"] == "-1" || result[j]["f_bytes_received"] == "-1" {
						bytesReceived = append(bytesReceived, "null")
					} else {
						bytesReceivedValue, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", (float64(stringToInt(result[j+1]["f_bytes_received"]))-float64(stringToInt(result[j]["f_bytes_received"])))/float64(timeDiff)/float64(1024)), 64)
						bytesReceived = append(bytesReceived, fmt.Sprintf("%v", bytesReceivedValue))
					}

					if result[j+1]["f_bytes_sent"] == "-1" || result[j]["f_bytes_sent"] == "-1" {
						bytesSent = append(bytesSent, "null")
					} else {
						bytesSentValue, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", (float64(stringToInt(result[j+1]["f_bytes_sent"]))-float64(stringToInt(result[j]["f_bytes_sent"])))/float64(timeDiff)/float64(1024)), 64)
						bytesSent = append(bytesSent, fmt.Sprintf("%v", bytesSentValue))
					}
				}

				var isRole []string
				if dbList[i]["f_role"] == "1" {
					isRole = append(isRole, "主库")
				} else {
					isRole = append(isRole, "从库")
				}
				hostJs := "<table><tr><th>类型</th><th>产品线</th><th>主机</th><th>端口</th><th>版本</th><th>主从</th><th>运行时间</th><th>开始时间</th><th>结束时间</th></tr>"
				hostJs += fmt.Sprintf("<tr><td>%v</td><td>%v</td><td>%v</td><td>%v</td><td>%v</td><td>%v</td><td>%v</td><td>%v</td><td>%v</td></tr>",
					"mysql", dbList[i]["f_class"], dbList[i]["f_ip"], dbList[i]["f_port"], dbList[i]["f_version"], isRole[0], diffDay, startTime.Format("2006-01-02 15:04:05"), endTime.Format("2006-01-02 15:04:05"))
				hostJs += "</table>"

				hostBar := charts.NewBar()
				//echarts=append(echarts, hostBar)
				//log.Println(hostBar)
				//log.Println(echarts)
				hostBar.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
				//hostBar.Width = "100%"
				hostBar.Height = "100px"
				hostBarChartID := fmt.Sprintf("%v_%v_%v_serverInfo", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
				hostBar.ChartID = hostBarChartID

				//js:=fmt.Sprintf("let id = document.getElementById('%v');    let code=';\n let data=%v;for(let i=0;i!=data.length;i++){\n         if (i==0){\n         code+='<tr><td>'+data[i]+'</td>';\n         }else if(i==data.length-1){\n             code+='<td>'+data[i]+'</td></tr>';\n         }else{\n             code+='<td>'+data[i]+'</td>';\n         }\n     }\n     id.innerHTML=code+'<table>';",hostBarChartID,hostInfo)
				hostBar.AddJSFuncs(fmt.Sprintf("document.getElementById('%v').innerHTML='%v';", hostBarChartID, hostJs))

				var mysqlDelay []string
				maxConnections := stringToInt(result[len(result)-1]["f_max_connections"])
				threadsConnected := stringToInt(result[len(result)-1]["f_threads_connected"])
				openTables := stringToInt(result[len(result)-1]["f_open_tables"])
				tableOpenCache := stringToInt(result[len(result)-1]["f_table_open_cache"])
				openFilesLimit := stringToInt(result[len(result)-1]["f_open_files_limit"])
				openFiles := stringToInt(result[len(result)-1]["f_open_files"])

				connPie := charts.NewPie()
				connPie.SetGlobalOptions(charts.TitleOpts{Title: "连接池使用"})
				connPie.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
				connPieId := fmt.Sprintf("%v_%v_%v_connPie", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
				connPie.ChartID = connPieId
				connPie.Height = "200px"
				//connPie.MarginLeft="200px"
				connPie.Width = "300px"
				//connPie.Border="1px solid #ccc"
				//connPieJs:=fmt.Sprintf("document.getElementById('%v').style.cssText='%v';document.getElementById('%v').parentNode.style.cssText='%v';",connPieId,"width:28%;margin-left:50px;",connPieId,"float:left;width:40%")
				connPieJs := fmt.Sprintf("document.getElementById('%v').style.cssText='%v';", connPieId, "border:1px solid #ccc;width:28%;margin-left:0px;")

				connPie.AddJSFuncs(connPieJs)
				connPie.Trigger = "item"
				connPie.Formatter = "{a} <br/>{b} : {c} ({d}%)"
				connPie.SetGlobalOptions(charts.LegendOpts{Bottom: "20", Left: "10", Orient: "vertical"})
				connPie.Add("连接池使用", map[string]interface{}{"可用连接": maxConnections - threadsConnected, "已用连接": threadsConnected},
					charts.LabelTextOpts{Show: true, Position: "inside", Formatter: "{d}%"},
				)

				tablePie := charts.NewPie()
				tablePie.SetGlobalOptions(charts.TitleOpts{Title: "表缓存数"})
				tablePie.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
				tablePieId := fmt.Sprintf("%v_%v_%v_tablePie", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
				tablePie.ChartID = tablePieId
				tablePie.Height = "200px"
				tablePie.Width = "300px"
				//connPie.Border="1px solid #ccc"
				//tablePieJs:=fmt.Sprintf("document.getElementById('%v').style.cssText='%v';document.getElementById('%v').parentNode.style.cssText='%v';",tablePieId,"border:1px solid #ccc;width:28%;margin-top:-200px;",tablePieId,"float:left;width:47%;margin:-47px")
				tablePieJs := fmt.Sprintf("document.getElementById('%v').style.cssText='%v';", tablePieId, "border:1px solid #ccc;width:28%;margin-top:-200px;")

				tablePie.AddJSFuncs(tablePieJs)
				tablePie.Trigger = "item"
				tablePie.Formatter = "{a} <br/>{b} : {c} ({d}%)"
				tablePie.SetGlobalOptions(charts.LegendOpts{Bottom: "20", Left: "10", Orient: "vertical"})

				tablePie.Add("表缓存使用", map[string]interface{}{"可用表缓存": tableOpenCache - openTables, "已打开表数": openTables},
					charts.LabelTextOpts{Show: true, Position: "inside", Formatter: "{d}%"},
				)

				filePie := charts.NewPie()
				filePie.SetGlobalOptions(charts.TitleOpts{Title: "打开文件数"})
				filePie.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
				filePieId := fmt.Sprintf("%v_%v_%v_filePie", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
				filePie.ChartID = filePieId
				filePie.Height = "200px"
				filePie.Width = "300px"
				//connPie.Border="1px solid #ccc"
				//filePieJs:=fmt.Sprintf("document.getElementById('%v').style.cssText='%v';document.getElementById('%v').parentNode.style.cssText='%v';",filePieId,"border:1px solid #ccc;width:28%;margin-top:-200px;margin-right:50px;",filePieId,"float:left;width:23%;margin:-23px")
				filePieJs := fmt.Sprintf("document.getElementById('%v').style.cssText='%v';", filePieId, "border:1px solid #ccc;width:28%;margin-top:-200px;margin-right:0px;")

				filePie.AddJSFuncs(filePieJs)
				filePie.Trigger = "item"
				filePie.Formatter = "{a} <br/>{b} : {c} ({d}%)"
				filePie.SetGlobalOptions(charts.LegendOpts{Bottom: "20", Left: "10", Orient: "vertical"})

				filePie.Add("打开文件数", map[string]interface{}{"可用文件句柄": openFilesLimit - openFiles, "已打开文件数": openFiles},
					charts.LabelTextOpts{Show: true, Position: "inside", Formatter: "{d}%"},
				)
				var markPoints = []charts.SeriesOptser{
					charts.MPNameTypeItem{Name: "最大值", Type: "max"},
					charts.MPNameTypeItem{Name: "平均值", Type: "average"},
					charts.MPNameTypeItem{Name: "最小值", Type: "min"},
					charts.MPStyleOpts{Label: charts.LabelTextOpts{Show: true}},
				}
				threadLineBar := charts.NewLine()

				threadLineBar.SetGlobalOptions(charts.TitleOpts{Title: "连接数(单位:个)"})
				threadLineBar.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
				threadLineBar.ChartID = fmt.Sprintf("%v_%v_%v_threadBar", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
				threadLineBar.Height = "300px"
				//threadLineBar.Width = "100%"
				threadLineBar.TextStyle.FontSize = 18
				threadLineBar.SetGlobalOptions(charts.ToolboxOpts{Show: true})
				threadLineBar.TextStyle.FontSize = 18
				threadLineBar.AddXAxis(xTime).
					AddYAxis("threads_running", threadsRunning, markPoints...).
					AddYAxis("threads_connect", threadsConnecteds, markPoints...).
					AddYAxis("threads_cached", threadsCached, markPoints...)

				//主从延迟
				dgDelayLineBar := charts.NewLine()

				dgDelayLineBar.SetGlobalOptions(charts.TitleOpts{Title: "延迟时间(单位:秒)"})
				dgDelayLineBar.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
				dgDelayLineBar.ChartID = fmt.Sprintf("%v_%v_%v_dgdelayBar", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
				dgDelayLineBar.Height = "300px"
				//dgDelayLineBar.Width = "100%"
				dgDelayLineBar.TextStyle.FontSize = 18
				dgDelayLineBar.SetGlobalOptions(charts.ToolboxOpts{Show: true})
				dgDelayLineBar.AddXAxis(xTime).AddYAxis("延迟时间", dgDelay, markPoints...)

				//qps
				qpsLineBar := charts.NewLine()

				qpsLineBar.SetGlobalOptions(charts.TitleOpts{Title: "QPS(单位:次/秒)"})
				qpsLineBar.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
				qpsLineBar.ChartID = fmt.Sprintf("%v_%v_%v_qpsBar", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
				qpsLineBar.Height = "300px"
				qpsLineBar.TextStyle.FontSize = 18
				//qpsLineBar.Width = "100%"
				qpsLineBar.SetGlobalOptions(charts.ToolboxOpts{Show: true})
				qpsLineBar.AddXAxis(xTime).
					AddYAxis("qps", qps, markPoints...)

				//tps
				tpsLineBar := charts.NewLine()

				tpsLineBar.SetGlobalOptions(charts.TitleOpts{Title: "TPS(单位:次/秒)"})
				tpsLineBar.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
				tpsLineBar.ChartID = fmt.Sprintf("%v_%v_%v_tpsBar", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
				tpsLineBar.Height = "300px"
				//tpsLineBar.Width = "100%"
				tpsLineBar.TextStyle.FontSize = 18
				tpsLineBar.SetGlobalOptions(charts.ToolboxOpts{Show: true})
				tpsLineBar.AddXAxis(xTime).
					AddYAxis("tps", tps, markPoints...)

				//dml
				dmlLineBar := charts.NewLine()

				dmlLineBar.SetGlobalOptions(charts.TitleOpts{Title: "DML(单位:次/秒)"})
				dmlLineBar.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
				dmlLineBar.ChartID = fmt.Sprintf("%v_%v_%v_dmlBar", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
				dmlLineBar.Height = "300px"
				dmlLineBar.TextStyle.FontSize = 18
				//dmlLineBar.Width = "100%"
				dmlLineBar.SetGlobalOptions(charts.ToolboxOpts{Show: true})
				dmlLineBar.AddXAxis(xTime).
					AddYAxis("insert", comInsert, markPoints...).
					AddYAxis("delete", comDelete, markPoints...).
					AddYAxis("update", comUpdate, markPoints...).
					AddYAxis("select", comSelect, markPoints...)
				//慢查
				slowQueriesLineBar := charts.NewLine()

				slowQueriesLineBar.SetGlobalOptions(charts.TitleOpts{Title: "慢查数(单位:个/秒)"})
				slowQueriesLineBar.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
				slowQueriesLineBar.ChartID = fmt.Sprintf("%v_%v_%v_slowqueriesBar", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
				slowQueriesLineBar.Height = "300px"
				slowQueriesLineBar.TextStyle.FontSize = 18
				//slowQueriesLineBar.Width = "100%"
				slowQueriesLineBar.SetGlobalOptions(charts.ToolboxOpts{Show: true})
				slowQueriesLineBar.AddXAxis(xTime).
					AddYAxis("slow_queries", slowQueries, markPoints...)

				//innodb buffer pool hit

				bufferPoolHitLineBar := charts.NewLine()

				bufferPoolHitLineBar.SetGlobalOptions(charts.TitleOpts{Title: "Innodb_buffer_pool_hit(单位:%)"})
				bufferPoolHitLineBar.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
				bufferPoolHitLineBar.ChartID = fmt.Sprintf("%v_%v_%v_bufferpoolhitBar", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
				bufferPoolHitLineBar.Height = "300px"
				bufferPoolHitLineBar.TextStyle.FontSize = 18
				//bufferPoolHitLineBar.Width = "100%"
				bufferPoolHitLineBar.SetGlobalOptions(charts.ToolboxOpts{Show: true})
				bufferPoolHitLineBar.AddXAxis(xTime).
					AddYAxis("Innodb_buffer_pool_hit", bufferPoolHit, markPoints...)

				//流量
				bytesLineBar := charts.NewLine()

				bytesLineBar.SetGlobalOptions(charts.TitleOpts{Title: "流量(单位:MB)"})
				bytesLineBar.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
				bytesLineBar.ChartID = fmt.Sprintf("%v_%v_%v_bytesBar", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
				bytesLineBar.Height = "300px"
				bytesLineBar.TextStyle.FontSize = 18
				// bytesLineBar.Width = "100%"
				bytesLineBar.SetGlobalOptions(charts.ToolboxOpts{Show: true})
				bytesLineBar.AddXAxis(xTime).
					AddYAxis("bytes_received", bytesReceived, markPoints...).
					AddYAxis("bytes_sent", bytesSent, markPoints...)

				p1.Add(
					hostBar,
					connPie,
					filePie,
					tablePie,
					threadLineBar,
				)
				if dbList[i]["f_role"] == "0" {
					p1.Add(dgDelayLineBar)
				}
				p1.Add(
					qpsLineBar,
					tpsLineBar,
					dmlLineBar,
					slowQueriesLineBar,
					bufferPoolHitLineBar,
					bytesLineBar,
				)

				for j := 0; j < len(result)-1; j++ {

					if dbList[i]["f_role"] == "0" {
						if result[j+1]["f_dg_delay"] == "-1" {
							mysqlDelay = append(mysqlDelay, "null")
						} else {
							mysqlDelay = append(mysqlDelay, result[j+1]["f_dg_delay"])
						}
					}
					//mysqlTimeDiff :=stringToInt(result[j+1]["f_check_time"])-stringToInt(result[j]["f_check_time"])

				}
			}
		}
	}
	htmlName:=fmt.Sprintf("databases_report_%v.html",time.Now().Format("2006_01_02"))
	f1, err1 := os.Create(htmlName)
	checkErr(dbInfo, err1)
	p1.Render(f1)





}