wine-server/utils/tables/wine.go

package tables

import (
	"Wine-Server/utils"
	"fmt"
)

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

	OrderCount  uint32
	TotalIncome float64
}

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," +
		"`density` DOUBLE DEFAULT 1," +
		"`picture` VARCHAR(128) NOT NULL," +
		"`time` DATETIME DEFAULT CURRENT_TIMESTAMP," +
		"`describe` VARCHAR(256)," +
		"`order` INT UNSIGNED DEFAULT 0," +
		"`income` DOUBLE DEFAULT 0);"
	_, err := utils.Mysql.Exec(sql)
	if err != nil {
		return err
	}
	return nil
}

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

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

func (row *WineTable) Update(args utils.JsonType) error {
	keys, values := utils.UnZip(args)
	sql := fmt.Sprintf("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...)
	if err != nil {
		return err
	}
	return nil
}

func (row *WineTable) Get() error {
	sql := "SELECT `name`, `price`, `density`, `picture`, IFNULL(`describe`, ''), `time`, `order`, `income` 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.Density, &row.Picture, &row.Describe, &row.Time, &row.OrderCount, &row.TotalIncome,
	)
	if err != nil {
		return err
	}
	return nil
}