// 查询
func Query(topN int) (*entity.SuperLottoSummary, error) {
// 连接数据库
db, err := conn.GetConn()
if err != nil {
log.Println("数据库初始化失败 : ", err.Error())
return nil, err
}
defer db.Close()
// 查询列表
list, err := queryList(topN, db)
if err != nil {
return nil, err
}
// 查询多少期没有出了
red, blue, err := queryDisappearCount(db)
if err != nil {
return nil, err
}
return &entity.SuperLottoSummary{List: list, Red: red, Blue: blue}, nil
}
// 分析1
func Analyze1(reds []int, blues []int) ([]entity.AnalyzeResult, error) {
if len(reds) == 0 && len(blues) == 0 {
return nil, errors.New("参数为空")
}
// log.Println("len:", reds)
condition := bytes.NewBufferString("")
params := make([]interface{}, 0)
sql := `
SELECT (LB.BallType - 1) * 100 + LB.Ball Num, COUNT(LB.Ball) Count
FROM
(
SELECT SL.ID
FROM SuperLotto SL
WHERE 1=1 %s
) SL
LEFT JOIN LotteryBall LB ON LB.MainID = SL.ID
GROUP BY (LB.BallType - 1) * 100 + LB.Ball`
// 查询红球
if len(reds) != 0 {
redsString := bytes.NewBufferString("")
for _, value := range reds {
if redsString.Len() > 0 {
redsString.WriteString(",")
}
_, err := redsString.WriteString(strconv.Itoa(value))
if err != nil {
return nil, err
}
}
condition.WriteString(fmt.Sprintf(`
AND EXISTS
(
SELECT 1
FROM LotteryBall LB
WHERE LB.MainID = SL.ID
AND LB.Ball IN (%s)
AND LB.RecordType = 2
AND LB.BallType = 1
GROUP BY LB.MainID
HAVING COUNT(LB.MainID) >= ?
) `, redsString.String()))
params = append(params, len(reds))
}
// 查询蓝球
if len(blues) != 0 {
bluesString := bytes.NewBufferString("")
for _, value := range blues {
if bluesString.Len() > 0 {
bluesString.WriteString(",")
}
_, err := bluesString.WriteString(strconv.Itoa(value))
if err != nil {
return nil, err
}
}
condition.WriteString(fmt.Sprintf(`
AND EXISTS
(
SELECT 1
FROM LotteryBall LB
WHERE LB.MainID = SL.ID
AND LB.Ball IN (%s)
AND LB.RecordType = 2
AND LB.BallType = 2
GROUP BY LB.MainID
HAVING COUNT(LB.MainID) >= ?
) `, bluesString.String()))
params = append(params, len(blues))
}
// 连接数据库
db, err := conn.GetConn()
if err != nil {
log.Println("数据库初始化失败 : ", err.Error())
return nil, err
}
defer db.Close()
s := fmt.Sprintf(sql, condition.String())
//log.Println("SQL:", s)
//log.Println("Parameter:", params)
// 查询
rows, err := db.Query(s, params...)
if err != nil {
return nil, err
}
defer rows.Close()
// 查询所有保存过的记录
results := make([]entity.AnalyzeResult, 0)
for rows.Next() {
var item entity.AnalyzeResult
err = item.ReadRows(rows)
if err != nil {
return nil, err
}
results = append(results, item)
}
return results, nil
}
// 保存开奖结果
func SaveData(fetched []entity.TwoColorBall) error {
// 连接数据库
db, err := conn.GetConn()
if err != nil {
log.Println("数据库初始化失败 : ", err.Error())
return err
}
defer db.Close()
// 查询
rows, err := db.Query("SELECT * FROM TwoColorBall ORDER BY No ASC")
if err != nil {
return err
}
defer rows.Close()
// 查询所有保存过的记录
saved := make([]entity.TwoColorBall, 0)
for rows.Next() {
var item entity.TwoColorBall
err = item.ReadRows(rows)
if err != nil {
return err
}
saved = append(saved, item)
}
// 过滤出需要保存的结果
toSave := make([]entity.TwoColorBall, 0)
for _, item := range fetched {
exists := false
for _, savedItem := range saved {
if item.No == savedItem.No {
exists = true
break
}
}
if !exists {
// 没保存过的结果添加进待保存队列
toSave = append(toSave, item)
}
}
//log.Println("需要新增的开奖结果:", toSave)
// 启动事务
transaction, err := db.Begin()
// 保存结果的语句
stmtTCB, err := transaction.Prepare("INSERT INTO TwoColorBall VALUES(?, NULL, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
if err != nil {
// 回滚事务
transaction.Rollback()
return err
}
defer stmtTCB.Close()
// 保存彩球的语句
stmtBall, err := transaction.Prepare("INSERT INTO LotteryBall VALUES(?, ?, 1, ?, ?)")
if err != nil {
// 回滚事务
transaction.Rollback()
return err
}
defer stmtBall.Close()
for _, item := range toSave {
// 保存结果
_, err = stmtTCB.Exec(item.ID, item.No, item.Date, item.Red1, item.Red2, item.Red3, item.Red4, item.Red5, item.Red6, item.Blue1)
if err != nil {
// 回滚事务
transaction.Rollback()
return err
}
// 保存彩球
_, err = stmtBall.Exec(crypto.GetUniqueInt64(), item.ID, item.Red1, 1)
if err != nil {
// 回滚事务
transaction.Rollback()
return err
}
_, err = stmtBall.Exec(crypto.GetUniqueInt64(), item.ID, item.Red2, 1)
if err != nil {
// 回滚事务
transaction.Rollback()
return err
}
_, err = stmtBall.Exec(crypto.GetUniqueInt64(), item.ID, item.Red3, 1)
if err != nil {
// 回滚事务
transaction.Rollback()
return err
}
_, err = stmtBall.Exec(crypto.GetUniqueInt64(), item.ID, item.Red4, 1)
if err != nil {
// 回滚事务
transaction.Rollback()
return err
}
_, err = stmtBall.Exec(crypto.GetUniqueInt64(), item.ID, item.Red5, 1)
if err != nil {
// 回滚事务
transaction.Rollback()
return err
}
_, err = stmtBall.Exec(crypto.GetUniqueInt64(), item.ID, item.Red6, 1)
if err != nil {
// 回滚事务
transaction.Rollback()
return err
}
_, err = stmtBall.Exec(crypto.GetUniqueInt64(), item.ID, item.Blue1, 2)
if err != nil {
// 回滚事务
transaction.Rollback()
return err
}
}
// 提交事务
transaction.Commit()
sql := `
UPDATE TwoColorBall
SET NextID = (SELECT B.ID FROM TwoColorBall B WHERE B.Date > TwoColorBall.Date ORDER BY B.Date ASC LIMIT 1)
WHERE NextID IS NULL`
_, err = db.Exec(sql)
if err != nil {
log.Print("update")
return err
}
if len(toSave) > 0 {
log.Println("新增双色球开奖结果", len(toSave), "组")
}
return nil
}