wine-server/utils/tables/advertise.go

package tables

import (
	"Wine-Server/utils"
	"database/sql"
)

type AdvertiseTable struct {
	Id       uint32         `json:"id"`
	Order    uint8          `json:"order"`
	Time     utils.TimeType `json:"time"`
	Src      string         `json:"src"`
	Type     bool           `json:"type"`     // true: pic, false: video
	Duration uint16         `json:"duration"` // ms
	Describe string         `json:"describe"`
}

func CreateAdvertiseTable() error {
	SQL := "CREATE TABLE IF NOT EXISTS `advertise`(" +
		"`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY," +
		"`order` TINYINT UNSIGNED NOT NULL," +
		"`time` DATETIME DEFAULT CURRENT_TIMESTAMP," +
		"`type` BOOL DEFAULT TRUE," +
		"`src` VARCHAR(128) NOT NULL," +
		"`duration` SMALLINT UNSIGNED DEFAULT 3000," +
		"`describe` VARCHAR(64) DEFAULT '');"
	_, err := utils.Mysql.Exec(SQL)
	return err
}

func (row *AdvertiseTable) Insert() error {
	SQL := "INSERT INTO `advertise`(`order`,`type`,`src`,`duration`,`describe`) VALUES(?,?,?,?,?);"
	pre, err := utils.Mysql.Prepare(SQL)
	if err != nil {
		return err
	}
	_, err = pre.Exec(row.Order, row.Type, row.Src, row.Duration, row.Describe)
	return err
}

func (row *AdvertiseTable) Delete() error {
	pre, err := utils.Mysql.Prepare("DELETE FROM `advertise` WHERE `id`=?;")
	if err != nil {
		return err
	}
	_, err = pre.Exec(row.Id)
	return err
}

func (row *AdvertiseTable) Update(args utils.JsonType) error {
	keys, values := utils.UnZip(args)
	SQL := utils.Format("UPDATE `advertise` SET %s WHERE `id`=%d;", utils.SqlFields(keys), row.Id)
	pre, err := utils.Mysql.Prepare(SQL)
	if err != nil {
		return err
	}
	_, err = pre.Exec(values...)
	return err
}

func (row *AdvertiseTable) UpdateSelf() error {
	SQL := "UPDATE `advertise` SET `order`=?,`src`=?,`type`=?,`duration`=?,`describe`=? WHERE `id`=?;"
	pre, err := utils.Mysql.Prepare(SQL)
	if err != nil {
		return err
	}
	_, err = pre.Exec(row.Order, row.Src, row.Type, row.Duration, row.Describe, row.Id)
	return err
}

func (row *AdvertiseTable) Get() error {
	SQL := "SELECT `order`,`time`,`src`,`type`,`duration`,`describe` FROM `advertise` WHERE `id`=?;"
	pre, err := utils.Mysql.Prepare(SQL)
	if err != nil {
		return err
	}
	err = pre.QueryRow(row.Id).Scan(&row.Order, &row.Time, &row.Src, &row.Type, &row.Duration, &row.Describe)
	return err
}

func AdvertiseListAll() ([]AdvertiseTable, error) {
	var res []AdvertiseTable
	SQL := "SELECT `id`,`order`,`time`,`src`,`type`,`duration`,`describe` FROM `advertise` ORDER BY `order`;"
	query, err := utils.Mysql.Query(SQL)
	if err != nil {
		return nil, err
	}
	for query.Next() {
		var one AdvertiseTable
		err = query.Scan(&one.Id, &one.Order, &one.Time, &one.Src, &one.Type, &one.Duration, &one.Describe)
		if err != nil {
			return nil, err
		}
		res = append(res, one)
	}
	return res, nil
}

func AdvertisesQuery(cond string, kind, limit, page int) (int, []AdvertiseTable, error) {
	SQL := "none"
	if kind == -1 {
		SQL = utils.Format("SELECT COUNT(`id`) AS `total` FROM `advertise` WHERE `describe` LIKE '%%%s%%';", cond)
	} else {
		SQL = utils.Format(
			"SELECT COUNT(`id`) AS `total` FROM `advertise` WHERE `type`=%d AND `describe` LIKE '%%%s%%';", kind, cond,
		)
	}
	total := 0
	err := utils.Mysql.QueryRow(SQL).Scan(&total)
	if err != nil {
		return 0, nil, err
	}
	if total == 0 {
		return 0, []AdvertiseTable{}, err
	}
	if kind == -1 {
		SQL = utils.Format(
			"SELECT `id`,`order`,`time`,`type`,`src`,`duration`,`describe` FROM `advertise` "+
				"WHERE `describe` LIKE '%%%s%%' LIMIT %d OFFSET %d;", cond, limit, (page-1)*limit,
		)
	} else {
		SQL = utils.Format(
			"SELECT `id`,`order`,`time`,`type`,`src`,`duration`,`describe` FROM `advertise` "+
				"WHERE `type`=%d AND `describe` LIKE '%%%s%%' LIMIT %d OFFSET %d;", kind, cond, limit, (page-1)*limit,
		)
	}
	var rows *sql.Rows
	res := make([]AdvertiseTable, 0)
	rows, err = utils.Mysql.Query(SQL)
	if err != nil {
		return 0, nil, err
	}
	for rows.Next() {
		tmp := AdvertiseTable{}
		_ = rows.Scan(&tmp.Id, &tmp.Order, &tmp.Time, &tmp.Type, &tmp.Src, &tmp.Duration, &tmp.Describe)
		res = append(res, tmp)
	}
	_ = rows.Close()
	return total, res, nil
}

func AdvertiseQueryMaxId() (uint32, error) {
	SQL := "SELECT MAX(`id`) FROM `advertise`;"
	var max uint32
	err := utils.Mysql.QueryRow(SQL).Scan(&max)
	if err != nil {
		return 0, err
	}
	return max, nil
}