122 lines
3.5 KiB
Go
122 lines
3.5 KiB
Go
package dbop
|
||
|
||
import (
|
||
"database/sql"
|
||
"fmt"
|
||
"time"
|
||
|
||
"github.com/shockliu/logger"
|
||
|
||
_ "github.com/go-sql-driver/mysql"
|
||
)
|
||
|
||
var (
|
||
MDb *sql.DB
|
||
err error // 错误信息
|
||
client map[string]string //设备信息，根据client_code查询
|
||
clientname map[string]string // 设备名称
|
||
|
||
)
|
||
|
||
// 初始化链接
|
||
func init() {
|
||
//"用户名:密码@[连接方式](主机名:端口号)/数据库名"
|
||
const (
|
||
USER_NAME = "eshc"
|
||
PASS_WORD = "Eshc88$*"
|
||
HOST = "47.118.40.174"
|
||
PORT = "3306"
|
||
DATABASE = "eshc_dev"
|
||
CHARSET = "utf8mb4"
|
||
)
|
||
|
||
// 豆曲咖数据库
|
||
//dsn := "eshc:Eshc88$*@tcp(47.118.40.174:3306)/eshc_dev"
|
||
dbDSN := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=%s&parseTime=true", USER_NAME, PASS_WORD, HOST, PORT, DATABASE, CHARSET)
|
||
// 打开连接失败
|
||
MDb, err = sql.Open("mysql", dbDSN)
|
||
//defer MysqlDb.Close();
|
||
if err != nil {
|
||
logger.Error("dbDSN: " + dbDSN)
|
||
panic("数据源配置不正确: " + err.Error())
|
||
}
|
||
|
||
// 最大连接数
|
||
MDb.SetMaxOpenConns(10)
|
||
// 闲置连接数
|
||
MDb.SetMaxIdleConns(5)
|
||
// 最大连接周期
|
||
MDb.SetConnMaxLifetime(100 * time.Second)
|
||
if err = MDb.Ping(); nil != err {
|
||
panic("数据库链接失败: " + err.Error())
|
||
}
|
||
}
|
||
|
||
func GetUserUnionID(openId string) (unionId string) {
|
||
MDb.QueryRow("select union_id from wechat_member where open_Id=?;", openId).Scan(&unionId)
|
||
return
|
||
}
|
||
|
||
func AddMpUser(openId, unionId string, scribeTime int32) {
|
||
var id int64
|
||
var uid string
|
||
// 已经有这个openID，则需要更新UnionID
|
||
err := MDb.QueryRow("select id,IFNULL(extra,'') from user_relation where openId=?;", openId).Scan(&id, &uid)
|
||
if err != nil {
|
||
logger.Errorf("获取用户%s信息失败:%s\n", openId, err)
|
||
} else if uid != unionId {
|
||
_, err = MDb.Exec("update user_relation set extra=? where openID=?;", unionId, openId)
|
||
if err != nil {
|
||
logger.Errorf("更新用户%s-%s信息失败:%s\n", openId, unionId, err)
|
||
}
|
||
}
|
||
|
||
if len(uid) > 0 {
|
||
_, err := MDb.Exec("UPDATE wxmp_user set statu=1,unionId=?,scribeTime=now() where openId=?;", unionId, openId)
|
||
if err != nil {
|
||
logger.Errorf("更新用户open[%s]union[%s]状态信息失败:%s\n", openId, unionId, err)
|
||
}
|
||
} else {
|
||
_, err := MDb.Exec("INSERT into wxmp_user (openId,unionId,scribeTime,statu)values(?,?,from_unixtime(?),1);", openId, unionId, scribeTime)
|
||
if err != nil {
|
||
logger.Errorf("插入公众号用户表失败:%s\n", err)
|
||
}
|
||
if len(unionId) > 0 {
|
||
err = MDb.QueryRow("select id from user_relation where extra=?;", unionId).Scan(&id)
|
||
if err == nil {
|
||
_, err = MDb.Exec("insert into user_relation (id,channel,openId,extra,`time`) values(?,1,?,?,now());", id, openId, unionId)
|
||
if err != nil {
|
||
logger.Debugf("插入user_relation表id[%d][%s][%s]失败:%s\n", id, openId, unionId, err)
|
||
}
|
||
return
|
||
}
|
||
}
|
||
|
||
//创建新用户
|
||
rst, err := MDb.Exec("insert into dqk_user (name,nickname,registerTime) values('公众号用户','公众号用户',now());")
|
||
if err != nil {
|
||
logger.Debugf("创建用户失败:%s\n", err)
|
||
return
|
||
}
|
||
id, err = rst.LastInsertId()
|
||
if err != nil {
|
||
logger.Debugf("获取新建用户ID失败:%s\n", err)
|
||
return
|
||
}
|
||
_, err = MDb.Exec("insert into user_relation (id,channel,openId,extra,`time`) values(?,1,?,?,now());", id, openId, unionId)
|
||
if err != nil {
|
||
logger.Debugf("插入user_relation表失败:%s\n", err)
|
||
return
|
||
}
|
||
}
|
||
}
|
||
|
||
func GetUserId(openid, unionId string) (id int) {
|
||
logger.Debugf("用户鉴权openid:%s\n", openid)
|
||
err := MDb.QueryRow("select a.user_id from user_account a where a.open_id = ?;", openid).Scan(&id)
|
||
if err != nil {
|
||
return -1
|
||
}
|
||
return id
|
||
}
|