golang打造基于mail的提醒服务

初识golang 

逻辑如下:

程序开启http服务器接收请求，且每隔20秒查询一次表auto_backup中flag为0的值，如果有查到且计划执行时间小于当前时间，则将表to_do的数据抓出来，通过邮件发送，完成后将数据表flag update 为1

表结构:

MySQL []> desc auto_backup;
+----------+---------------+------+-----+---------+----------------+
| Field    | Type          | Null | Key | Default | Extra          |
+----------+---------------+------+-----+---------+----------------+
| id       | int(11)       | NO   | PRI | NULL    | auto_increment |
| datetime | int(11)       | NO   |     | NULL    |                |
| to_do    | varchar(2000) | NO   |     | NULL    |                |
| flag     | int(11)       | NO   | MUL | NULL    |                |
+----------+---------------+------+-----+---------+----------------+
4 rows in set (0.04 sec)

表数据:

MySQL []> SELECT 
    -> `id`,
    -> FROM_UNIXTIME(`auto_backup`.`datetime`) AS `datetimes`,
    -> `auto_backup`.`to_do` AS `to_do`,
    -> `auto_backup`.`flag` AS `flag`
    -> FROM
    -> `auto_backup`
    -> order by datetimes desc
    -> limit 1;
+----+---------------------+-----------------+------+
| id | datetimes           | to_do           | flag |
+----+---------------------+-----------------+------+
| 63 | 2019-04-02 17:30:00 | 哈哈哈测试      |    1 |
+----+---------------------+-----------------+------+
1 row in set (0.04 sec)

 

效果如下:

添加记录

发送记录 

服务器log:

代码如下:

package main

// 导入相关包
import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "gopkg.in/gomail.v2"
    "log"
    "net/http"
    "strconv"
    "time"
)

// http 服务器
func Httpd_beiwang(w http.ResponseWriter, r *http.Request) {
    r.ParseForm()

    // 赛选掉访问的url路径为 /favicon.ico
    if "/favicon.ico" != r.URL.Path {
        log.Println(r.Form)

        // 获取url参数
        time_value, timeOK := r.Form["time"]
        beiwang_value, beiwangOK := r.Form["beiwang"]

        // 如果两个参数正确，则判断参数正确，需要将定时任务写到数据库中去
        if timeOK && beiwangOK {
            log.Println("参数判断ok,参数值为:")

            log.Println(time_value[0])
            log.Println(beiwang_value[0])

            // 定义初始时间
            const base_format = ""2006-01-02 15:04""

            // 转化为unix时间
            loc, _ := time.LoadLocation("Local")
            parse_time, err := time.ParseInLocation(base_format, time_value[0], loc)

            unix_parse_time := parse_time.Unix()

            log.Println("Unix 时间为: ", unix_parse_time)

            if err != nil {
                fmt.Println(err)
            }

            // 需要Insert的sql
            insert_sql := fmt.Sprintf("insert into auto_backup (datetime , to_do , flag) values (%d,%s , 0);", unix_parse_time, beiwang_value[0])

            log.Println("执行的sql: ", insert_sql)

            // 执行sql
            results, err := db.Exec(insert_sql)

            if err != nil {
                log.Println("sql执行错误,错误内容为:")
                log.Println(err)
            } else {
                log.Println("执行成功,", results)
                fmt.Fprintf(w, "request ok")
            }

        } else {
            fmt.Fprintf(w, "request error")
        }
    }
}

// 发送邮件
func SendMail(body string) error {
    mailConn := map[string]string{
        "user": "from_user",
        "pass": "password",
        "host": "smtp",
        "port": "port",
    }

    port, _ := strconv.Atoi(mailConn["port"])

    m := gomail.NewMessage()
    //m.SetHeader("From" , "XD Game" + "<" mailConn["user"] + ">")
    m.SetHeader("From", "XD Game"+"<"+mailConn["user"]+">")
    m.SetHeader("To", "to_user")
    m.SetHeader("Subject", "执行计划")
    m.SetBody("text/html", body)

    d := gomail.NewDialer(mailConn["host"], port, mailConn["user"], mailConn["pass"])

    err := d.DialAndSend(m)

    return err
}

// 返回需要执行定时任务的id,返回值为Int数组
func Deal_Exec_id(db *sql.DB) []int {
    var send_id []int
    // 获取当前时间戳
    now_t := time.Now()
    // 执行查询sql
    select_datetime := fmt.Sprintf("select id from auto_backup where flag = 0 and datetime <= %d", now_t.Unix())

    // 执行sql
    select_rows, err := db.Query(select_datetime)
    log.Println(select_datetime)

    if err != nil {
        log.Println(err)
    }

    for select_rows.Next() {
        var id int

        if err := select_rows.Scan(&id); err != nil {
            log.Println(err)
        }

        //fmt.Println(id)
        // 将查询的结果追加到send_id中
        send_id = append(send_id, id)

    }
    log.Println("查询结果返回ID", send_id)

    // 将send_id数组返回
    return send_id
}

func Deal_SendMail(db *sql.DB, send_id []int) {

    var to_do string

    for id := range (send_id) {
        fmt.Println(send_id[id])

        // 根据send_id查询 messages
        select_sql := fmt.Sprintf("SELECT to_do FROM auto_backup WHERE id = '%d';", send_id[id])
        log.Println(select_sql)

        err := db.QueryRow(select_sql).Scan(&to_do)

        if err != nil {
            log.Println(err)
        }

        log.Println("发送的内容", to_do)
        SendMail(to_do)

        // 执行 update sql
        update_sql := fmt.Sprintf("UPDATE auto_backup SET flag = 1 WHERE id = '%d'", send_id[id])
        log.Println(update_sql)

        results, err := db.Exec(update_sql)

        if err != nil {
            log.Println(err)
        } else {
            log.Println(results)
        }
    }
}



func start_jobs() {
    
    for {
        log.Println("Auto Time jobs Start")
        send_id := Deal_Exec_id(db)
        if 0 == len(send_id) {
            log.Println("未找到相关jobs")
        } else {
            Deal_SendMail(db, send_id)
        }
        // 每隔20秒刷新一次数据库，查看是否存在新数据
        time.Sleep(time.Second * 20)
    }
}

// 定义*sql.DB 变量
var db *sql.DB

func main() {
    // 连接mysql
    var err error
    c := make(chan int, 1)
    db, err = sql.Open("mysql", "username:password@tcp(host)/database_name")

    // 在main退出时关闭链接
    defer db.Close()

    if err != nil {
        log.Println("Connect DB failed")
        log.Println(err)
    }

    // 开启http服务器
    http.HandleFunc("/", Httpd_beiwang)

    // 开启协程
    go func() {

        err = http.ListenAndServe("0.0.0.0:9090", nil)

        if err != nil {
            log.Fatal(err)
        }
    }()

    go start_jobs()
    <- c
}