package mssql
import (
"crypto/tls"
"database/sql"
"gopkg.in/gomail.v2"
"log"
"strconv"
)
// SendMail
func SendMail(mailTo []string, subject string, body string) error {
// 定义邮箱服务器链接信息，如果是阿里邮箱，pass填写密码
mailConn := map[string]string{
"user": "xxx@xx.xxx",
"pass": "xxx",
"host": "smtp.xx.xxx",
"port": "25",
}
port, _ := strconv.Atoi(mailConn["port"]) // 转换端口类型为int
m := gomail.NewMessage()
// 这种方式添加别名，即“XD Game"， 可以直接用<code>m.SetHeader("From",mailConn["user"])</code>
m.SetHeader("From", ""+"<"+mailConn["user"]+">")
m.SetHeader("To", mailTo...) // 发送给多个用户
m.SetHeader("Subject", subject) // 设置邮箱主题
m.SetBody("text/html", body) // 设置邮箱正文
d := gomail.NewDialer(mailConn["host"], port, mailConn["user"], mailConn["pass"])
d.TLSConfig = &tls.Config{InsecureSkipVerify: true}
err := d.DialAndSend(m)
return err
}
// jobData
func GetHelpJob(mssqlConn string, param HelpJop, procName string) (results []map[string]interface{}, err error) {
db := MssqlConn(mssqlConn)
defer func(db *sql.DB) {
err := db.Close()
if err != nil {
}
}(db)
// 构造参数
m1 := make(map[string]string)
m1["@enabled"] = strconv.FormatInt(param.Enable, 10)
sqls := GetExecProcSql(procName, m1, false)
rows, err := db.Query(sqls)
if err != nil {
log.Fatal(err.Error())
return nil, err
} else {
cols, err := rows.Columns()
if err != nil {
log.Fatal("row.colums failed:", err.Error())
return nil, err
}
for rows.Next() {
scans := make([]interface{}, len(cols))
row := make(map[string]interface{})
for i := range scans {
scans[i] = &scans[i]
}
err := rows.Scan(scans...)
if err != nil {
log.Fatal(err.Error())
return nil, err
}
for i, v := range scans {
row[cols[i]] = v
}
results = append(results, row)
}
}
defer func(rows *sql.Rows) {
err := rows.Close()
if err != nil {
}
}(rows)
return results, nil
}
// 处理数据
func GetData() string {
// 获取数据
procName := "msdb..sp_help_job"
param := HelpJop{
Enable: 1,
}
results, err := GetHelpJob(MssqlDatabase, param, procName)
if err != nil {
log.Fatal(err.Error())
}
// 处理数据,html格式
//var header = `<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8" /></head>`
var html string
html = "<table border=\"1\" style=\"text-align:center;\"><tr><th>outcome</th><th>job_name</th><th>last_run_date</th><th>last_run_time</th><th>next_run_date</th><th>next_run_time</th><th>current_status</th><th>has_schedule</th></tr>"
for _, v := range results {
if v["last_run_outcome"].(int64) == 0 {
html += "<tr style=\"background-color:red;\">"
} else if v["last_run_outcome"].(int64) == 1 {
html += "<tr>"
} else if v["last_run_outcome"].(int64) == 3 {
html += "<tr style=\"background-color:#ccc;\">"
} else {
html += "<tr style=\"background-color:pink;\">"
}
if v["last_run_outcome"].(int64) == 0 {
html += "<td>Failed</td>"
} else if v["last_run_outcome"].(int64) == 1 {
html += "<td>Succeeded</td>"
} else if v["last_run_outcome"].(int64) == 3 {
html += "<td>Canceled</td>"
} else if v["last_run_outcome"].(int64) == 5 {
html += "<td>Unknown</td>"
} else {
html += "<td>" + strconv.FormatInt(v["last_run_outcome"].(int64), 10) + "</td>"
}
html = html + "<td>" + v["name"].(string) + "</td>" +
"<td>" + strconv.FormatInt(v["last_run_date"].(int64), 10) + "</td>" +
"<td>" + strconv.FormatInt(v["last_run_time"].(int64), 10) + "</td>" +
"<td>" + strconv.FormatInt(v["next_run_date"].(int64), 10) + "</td>" +
"<td>" + strconv.FormatInt(v["next_run_time"].(int64), 10) + "</td>" +
"<td>" + strconv.FormatInt(v["current_execution_status"].(int64), 10) + "</td>" +
"<td>" + strconv.FormatInt(v["has_schedule"].(int64), 10) + "</td>" + "</tr>"
}
html += "</table></body></html>"
//body := header + html
return html
}
