wine-server/utils/tables/device.go

package tables

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

type deviceWine struct {
	Id       uint16  `json:"id"`
	Name     string  `json:"name"`
	Price    uint16  `json:"price"`
	Density  float64 `json:"density"`
	Picture  string  `json:"picture"`
	Describe string  `json:"describe"`
	Remain   uint16  `json:"remain"`
}

func (dv *deviceWine) Get() error {
	if dv.Id == 0 {
		return nil
	}
	src := &WineTable{Id: dv.Id}
	err := src.Get()
	if err != nil {
		return err
	}
	dv.Name = src.Name
	dv.Price = src.Price
	dv.Density = src.Density
	dv.Picture = src.Picture
	dv.Describe = src.Describe
	return nil
}

type DeviceTable struct {
	Id    string
	Addr  string
	First utils.TimeType
	Last  utils.TimeType
	Wines [4]deviceWine // db col-name: wine1, remain1, ...

	Manager     string
	Mark        string
	OrderCount  uint32
	TotalIncome float64
}

func CreateDeviceTable() error {
	sql := "CREATE TABLE IF NOT EXISTS `device`(" +
		"`id` VARCHAR(32) PRIMARY KEY," +
		"`addr` VARCHAR(128) NOT NULL," +
		"`first` DATETIME DEFAULT CURRENT_TIMESTAMP," +
		"`last` DATETIME DEFAULT CURRENT_TIMESTAMP," +
		"`wine1` SMALLINT UNSIGNED,`remain1` SMALLINT UNSIGNED DEFAULT 0," +
		"`wine2` SMALLINT UNSIGNED,`remain2` SMALLINT UNSIGNED DEFAULT 0," +
		"`wine3` SMALLINT UNSIGNED,`remain3` SMALLINT UNSIGNED DEFAULT 0," +
		"`wine4` SMALLINT UNSIGNED,`remain4` SMALLINT UNSIGNED DEFAULT 0," +
		"`manager` VARCHAR(16),`mark` 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 *DeviceTable) Insert() error {
	pre, err := utils.Mysql.Prepare("INSERT INTO `device` (`id`, `addr`) VALUES (?, ?);")
	if err != nil {
		return err
	}
	_, err = pre.Exec(row.Id, row.Addr)
	if err != nil {
		return err
	}
	return nil
}

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

func (row *DeviceTable) Update(args utils.JsonType) error {
	keys, values := utils.UnZip(args)
	sql := fmt.Sprintf("UPDATE `device` SET %s WHERE `id`='%s';", 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 *DeviceTable) Get() error {
	sql := "SELECT `addr`, `first`, `last`, IFNULL(`wine1`, 10100), `remain1`, IFNULL(`wine2`, 10100), `remain2`, " +
		"IFNULL(`wine3`, 10100), `remain3`, IFNULL(`wine4`, 10100), `remain4`, IFNULL(`manager`, '-'), " +
		"IFNULL(`mark`, '-'), `order`, `income` " +
		"FROM `device` WHERE `id`=?;"
	pre, err := utils.Mysql.Prepare(sql)
	if err != nil {
		return err
	}
	err = pre.QueryRow(row.Id).Scan(
		&row.Addr, &row.First, &row.Last, &row.Wines[0].Id, &row.Wines[0].Remain,
		&row.Wines[1].Id, &row.Wines[1].Remain, &row.Wines[2].Id, &row.Wines[2].Remain,
		&row.Wines[3].Id, &row.Wines[3].Remain, &row.Manager, &row.Mark, &row.OrderCount, &row.TotalIncome,
	)
	if err != nil {
		return err
	}
	return nil
}