wine-server/utils/tables/device.go

package tables

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

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

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

type DeviceTable struct {
	Id    string
	Addr  string
	First utils.TimeType
	Last  utils.TimeType
	Wines [4]deviceWine

	Manager string
	Mark    string
	Order   uint32
	Income  uint64
}

func CreateDeviceTable() error {
	SQL := "CREATE TABLE IF NOT EXISTS `device`(" +
		"`id` VARCHAR(32) PRIMARY KEY," +
		"`addr` VARCHAR(128) DEFAULT ''," +
		"`first` DATETIME DEFAULT CURRENT_TIMESTAMP," +
		"`last` DATETIME DEFAULT CURRENT_TIMESTAMP," +
		"`wine1` SMALLINT UNSIGNED DEFAULT 10100,`remain1` SMALLINT UNSIGNED DEFAULT 0,`ppv1` SMALLINT UNSIGNED DEFAULT 2," +
		"`wine2` SMALLINT UNSIGNED DEFAULT 10100,`remain2` SMALLINT UNSIGNED DEFAULT 0,`ppv2` SMALLINT UNSIGNED DEFAULT 2," +
		"`wine3` SMALLINT UNSIGNED DEFAULT 10100,`remain3` SMALLINT UNSIGNED DEFAULT 0,`ppv3` SMALLINT UNSIGNED DEFAULT 2," +
		"`wine4` SMALLINT UNSIGNED DEFAULT 10100,`remain4` SMALLINT UNSIGNED DEFAULT 0,`ppv4` SMALLINT UNSIGNED DEFAULT 2," +
		"`manager` VARCHAR(16) DEFAULT '',`mark` VARCHAR(256) DEFAULT ''," +
		"`order` INT UNSIGNED DEFAULT 0,`income` BIGINT UNSIGNED DEFAULT 0);"
	_, err := utils.Mysql.Exec(SQL)
	return err
}

func (row *DeviceTable) Insert() error {
	pre, err := utils.Mysql.Prepare("INSERT INTO `device`(`id`) VALUES(?);")
	if err != nil {
		return err
	}
	_, err = pre.Exec(row.Id)
	return err
}

// Delete deprecated
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)
	return err
}

func (row *DeviceTable) Update(args utils.JsonType) error {
	keys, values := utils.UnZip(args)
	SQL := utils.Format("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...)
	return err
}

func (row *DeviceTable) UpdateSelf() error {
	SQL := "UPDATE `device` SET `addr`=?,`last`=?,`wine1`=?,`remain1`=?,`ppv1`=?,`wine2`=?,`remain2`=?,`ppv2`=?," +
		"`wine3`=?,`remain3`=?,`ppv3`=?,`wine4`=?,`remain4`=?,`ppv4`=?,`manager`=?,`mark`=?,`order`=?," +
		"`income`=? WHERE `id`=?;"
	pre, err := utils.Mysql.Prepare(SQL)
	if err != nil {
		return err
	}
	row.Last = utils.TimeNow()
	_, err = pre.Exec(
		row.Addr, row.Last,
		row.Wines[0].Id, row.Wines[0].Remain, row.Wines[0].Ppv,
		row.Wines[1].Id, row.Wines[1].Remain, row.Wines[1].Ppv,
		row.Wines[2].Id, row.Wines[2].Remain, row.Wines[2].Ppv,
		row.Wines[3].Id, row.Wines[3].Remain, row.Wines[3].Ppv,
		row.Manager, row.Mark, row.Order, row.Income, row.Id,
	)
	return err
}

func (row *DeviceTable) Get() error {
	SQL := "SELECT `addr`,`first`,`last`,`wine1`,`remain1`,`ppv1`,`wine2`,`remain2`,`ppv2`," +
		"`wine3`,`remain3`,`ppv3`,`wine4`,`remain4`,`ppv4`,`manager`,`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[0].Ppv,
		&row.Wines[1].Id, &row.Wines[1].Remain, &row.Wines[1].Ppv,
		&row.Wines[2].Id, &row.Wines[2].Remain, &row.Wines[2].Ppv,
		&row.Wines[3].Id, &row.Wines[3].Remain, &row.Wines[3].Ppv,
		&row.Manager, &row.Mark, &row.Order, &row.Income,
	)
	return err
}

func DevicesQuery(manager, cond string, limit, page int) (int, []utils.JsonType, error) {
	SQL, like := "none", utils.Format("%%%s%%", cond)
	if manager == "" { // not assigned
		SQL = "SELECT COUNT(`id`) AS `total` FROM `device` WHERE `manager`='' AND (`id` LIKE ? OR `addr` LIKE ? OR `mark` LIKE ?);"
	} else if manager == "*" { // all
		SQL = "SELECT COUNT(`id`) AS `total` FROM `device` WHERE `id` LIKE ? OR `addr` LIKE ? OR `mark` LIKE ?;"
	} else { // specific one
		SQL = "SELECT COUNT(`id`) AS `total` FROM `device` WHERE `manager`=? AND (`id` LIKE ? OR `addr` LIKE ? OR `mark` LIKE ?);"
	}
	pre, err := utils.Mysql.Prepare(SQL)
	if err != nil {
		return 0, nil, err
	}
	total := 0
	if manager == "" || manager == "*" {
		err = pre.QueryRow(like, like, like).Scan(&total)
	} else {
		err = pre.QueryRow(manager, like, like, like).Scan(&total)
	}
	if err != nil {
		return 0, nil, err
	}
	if total == 0 {
		return 0, []utils.JsonType{}, err
	}
	if manager == "" { // not assigned
		SQL = "SELECT d.`id`,d.`addr`,d.`first`,d.`last`,d.`mark`,m.`id`,m.`name` FROM `device` AS d LEFT JOIN `manager` AS m " +
			"ON d.`manager`=m.`id` WHERE d.`manager`='' AND (d.`id` LIKE ? OR d.`addr` LIKE ? OR d.`mark` LIKE ?) LIMIT ? OFFSET ?;"
	} else if manager == "*" { // all
		SQL = "SELECT d.`id`,d.`addr`,d.`first`,d.`last`,d.`mark`,m.`id`,m.`name` FROM `device` AS d LEFT JOIN `manager` AS m " +
			"ON d.`manager`=m.`id` WHERE d.`id` LIKE ? OR d.`addr` LIKE ? OR d.`mark` LIKE ? LIMIT ? OFFSET ?;"
	} else { // specific one
		SQL = "SELECT d.`id`,d.`addr`,d.`first`,d.`last`,d.`mark`,m.`id`,m.`name` FROM `device` AS d LEFT JOIN `manager` AS m " +
			"ON d.`manager`=m.`id` WHERE d.`manager`=? AND (d.`id` LIKE ? OR d.`addr` LIKE ? OR d.`mark` LIKE ?) LIMIT ? OFFSET ?;"
	}
	pre, err = utils.Mysql.Prepare(SQL)
	if err != nil {
		return 0, nil, err
	}
	var rows *sql.Rows
	if manager == "" || manager == "*" {
		rows, err = pre.Query(like, like, like, limit, (page-1)*limit)
	} else {
		rows, err = pre.Query(manager, like, like, like, limit, (page-1)*limit)
	}
	if err != nil {
		return 0, nil, err
	}
	res := make([]utils.JsonType, 0)
	for rows.Next() {
		tId, tAddr, tMark, tMid, tManager := "", "", "", "", ""
		tFirst, tLast := utils.TimeNow(), utils.TimeNow()
		_ = rows.Scan(&tId, &tAddr, &tFirst, &tLast, &tMark, &tMid, &tManager)
		res = append(res, utils.JsonType{
			"id": tId, "addr": tAddr, "first": tFirst, "last": tLast, "mark": tMark,
			"manager": utils.JsonType{"id": tMid, "name": tManager},
		})
	}
	_ = rows.Close()
	return total, res, nil
}

func DevicesAssignManager(ids []string, manager string) error {
	SQL := utils.Format("UPDATE `device` SET `manager`='%s' WHERE `id` IN (%s);", manager, utils.SqlStringListJoin(ids))
	_, err := utils.Mysql.Exec(SQL)
	return err
}

func DevicesWithTheWine(wid uint16) ([]string, error) {
	SQL := utils.Format(
		"SELECT `id` FROM `device` WHERE `wine1`=%d OR `wine2`=%d OR `wine3`=%d OR `wine4`=%d;",
		wid, wid, wid, wid,
	)
	rows, err := utils.Mysql.Query(SQL)
	if err != nil {
		return nil, err
	}
	res := make([]string, 0)
	for rows.Next() {
		var id string
		_ = rows.Scan(&id)
		res = append(res, id)
	}
	_ = rows.Close()
	return res, nil
}