Example #1
0
//  查询
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
}
Example #2
0
//  分析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
}
Example #3
0
File: sync.go Project: nzai/lottery
//  保存开奖结果
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
}