wine-server/utils/tables/wine.go

package tables

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

type WineTable struct {
	Id       uint16
	Name     string
	Price    uint16
	Degree   uint16
	Density  uint16
	Picture  string
	Time     utils.TimeType
	Describe string
	Deleted  bool

	Order  uint32
	Income uint64
}

func CreateWineTable() error {
	SQL := "CREATE TABLE IF NOT EXISTS `wine`(" +
		"`id` SMALLINT UNSIGNED PRIMARY KEY," +
		"`name` VARCHAR(16)  NOT NULL," +
		"`price` SMALLINT UNSIGNED DEFAULT 9999," +
		"`degree` SMALLINT UNSIGNED DEFAULT 0," +
		"`density` SMALLINT UNSIGNED DEFAULT 1000," +
		"`picture` VARCHAR(128) NOT NULL," +
		"`time` DATETIME DEFAULT CURRENT_TIMESTAMP," +
		"`describe` VARCHAR(256) DEFAULT ''," +
		"`order` INT UNSIGNED DEFAULT 0," +
		"`income` BIGINT UNSIGNED DEFAULT 0," +
		"`deleted` TINYINT(1) DEFAULT 0);"
	_, err := utils.Mysql.Exec(SQL)
	return err
}

func (row *WineTable) Insert() error {
	SQL := "INSERT INTO `wine` (`id`,`name`,`price`,`degree`,`density`,`picture`,`describe`) VALUES (?,?,?,?,?,?,?);"
	pre, err := utils.Mysql.Prepare(SQL)
	if err != nil {
		return err
	}
	_, err = pre.Exec(row.Id, row.Name, row.Price, row.Degree, row.Density, row.Picture, row.Describe)
	return err
}

func (row *WineTable) Delete() error {
	row.Deleted = true
	return row.UpdateSelf()
}

func (row *WineTable) Update(args utils.JsonType) error {
	keys, values := utils.UnZip(args)
	SQL := utils.Format("UPDATE `wine` 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 *WineTable) UpdateSelf() error {
	SQL := "UPDATE `wine` SET `name`=?,`price`=?,`degree`=?,`density`=?,`picture`=?,`describe`=?,`order`=?,`income`=?,`deleted`=? WHERE `id`=?;"
	pre, err := utils.Mysql.Prepare(SQL)
	if err != nil {
		return err
	}
	_, err = pre.Exec(row.Name, row.Price, row.Degree, row.Density, row.Picture, row.Describe, row.Order, row.Income, row.Deleted, row.Id)
	return err
}

func (row *WineTable) Get() error {
	SQL := "SELECT `name`,`price`,`degree`,`density`,`picture`,`describe`,`time`,`order`,`income`,`deleted` FROM `wine` WHERE `id`=?;"
	pre, err := utils.Mysql.Prepare(SQL)
	if err != nil {
		return err
	}
	err = pre.QueryRow(row.Id).Scan(
		&row.Name, &row.Price, &row.Degree, &row.Density, &row.Picture,
		&row.Describe, &row.Time, &row.Order, &row.Income, &row.Deleted,
	)
	return err
}

func extractJson(wine WineTable) utils.JsonType {
	return utils.JsonType{
		"id": wine.Id, "name": wine.Name, "time": wine.Time, "price": wine.Price,
		"degree": wine.Degree, "density": wine.Density, "picture": wine.Picture, "describe": wine.Describe,
	}
}

func WinesQuery(cond string, limit, page int) (int, []utils.JsonType, error) {
	SQL, like := "none", utils.Format("%%%s%%", cond)
	if page == 1 {
		SQL = "SELECT COUNT(`id`) AS `total` FROM `wine` " +
			"WHERE (`id`=10100 OR `deleted`=FALSE) AND (`name` LIKE ? OR `describe` LIKE ?);"
	} else {
		SQL = "SELECT COUNT(`id`) AS `total` FROM `wine` WHERE `deleted`=FALSE AND (`name` LIKE ? OR `describe` LIKE ?);"
	}
	pre, err := utils.Mysql.Prepare(SQL)
	if err != nil {
		return 0, nil, err
	}
	total := 0
	err = pre.QueryRow(like, like).Scan(&total)
	if err != nil {
		return 0, nil, err
	}
	if page == 1 {
		SQL = "SELECT `id`,`name`,`time`,`price`,`degree`,`density`,`picture`,`describe` FROM `wine` " +
			"WHERE (`id`=10100 OR `deleted`=FALSE) AND (`name` LIKE ? OR `describe` LIKE ?) LIMIT ? OFFSET ?;"
	} else {
		SQL = "SELECT `id`,`name`,`time`,`price`,`degree`,`density`,`picture`,`describe` FROM `wine` " +
			"WHERE `deleted`=FALSE AND (`name` LIKE ? OR `describe` LIKE ?) LIMIT ? OFFSET ?;"
	}
	pre, err = utils.Mysql.Prepare(SQL)
	if err != nil {
		return 0, nil, err
	}
	var rows *sql.Rows
	res := make([]utils.JsonType, 0)
	rows, err = pre.Query(like, like, limit, (page-1)*limit)
	if err != nil {
		return 0, nil, err
	}
	for rows.Next() {
		tmp := WineTable{}
		_ = rows.Scan(&tmp.Id, &tmp.Name, &tmp.Time, &tmp.Price, &tmp.Degree, &tmp.Density, &tmp.Picture, &tmp.Describe)
		res = append(res, extractJson(tmp))
	}
	_ = rows.Close()
	return total, res, nil
}

func WinesDelete(ids []uint16) error {
	SQL := utils.Format("UPDATE `wine` SET `deleted`=TRUE WHERE `id` IN (%s);", utils.SqlUint16ListJoin(ids))
	_, err := utils.Mysql.Exec(SQL)
	return err
}

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

func WinesQueryByIdOrName(cond string) ([]utils.JsonType, error) {
	like := utils.Format("%%%s%%", cond)
	SQL := "SELECT `id`,`name` FROM `wine` WHERE `id`!=10100 AND (`id` LIKE ? OR `name` LIKE ?) LIMIT 10;"
	rows, err := utils.Mysql.Query(SQL, like, like)
	if err != nil {
		return nil, err
	}
	res := make([]utils.JsonType, 0)
	for rows.Next() {
		tid, tna := "", ""
		_ = rows.Scan(&tid, &tna)
		res = append(res, utils.JsonType{"id": tid, "name": tna})
	}
	_ = rows.Close()
	return res, nil
}