All/Go/Wine-Server/utils/tables/warn.go

package tables

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

type WarnTable struct {
	Id      uint32
	Device  string
	Manager string
	Time    utils.TimeType
	Deal    bool
}

func CreateWarnTable() error {
	SQL := "CREATE TABLE IF NOT EXISTS `warn`(" +
		"`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL," +
		"`device` VARCHAR(32) NOT NULL," +
		"`manager` VARCHAR(16) NOT NULL," +
		"`time` DATETIME DEFAULT CURRENT_TIMESTAMP," +
		"`deal` BOOL DEFAULT FALSE);"
	_, err := utils.Mysql.Exec(SQL)
	return err
}

func (row *WarnTable) Insert() error {
	SQL := "INSERT INTO `warn` (`device`,`manager`) VALUES (?,?);"
	pre, err := utils.Mysql.Prepare(SQL)
	if err != nil {
		return err
	}
	_, err = pre.Exec(row.Device, row.Manager)
	return err
}

// Delete deprecated
func (row *WarnTable) Delete() error {
	_, err := utils.Mysql.Exec("DELETE FROM `warn` WHERE `id`=?;", row.Id)
	return err
}

func (row *WarnTable) Update(args utils.JsonType) error {
	keys, values := utils.UnZip(args)
	SQL := utils.Format("UPDATE `warn` 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 *WarnTable) UpdateSelf() error {
	SQL := "UPDATE `warn` SET `device`=?,`manager`=?,`time`=?,`deal`=? WHERE `id`=?;"
	row.Time = utils.TimeNow()
	_, err := utils.Mysql.Exec(SQL, row.Device, row.Manager, row.Time, row.Deal, row.Id)
	return err
}

func (row *WarnTable) Get() error {
	SQL := "SELECT `device`,`manager`,`time`,`deal` FROM `warn` WHERE `id`=?;"
	err := utils.Mysql.QueryRow(SQL, row.Id).Scan(&row.Device, &row.Manager, &row.Time, &row.Deal)
	return err
}

func (row *WarnTable) GetByDeviceAndManager() error {
	SQL := "SELECT `id`,`time`,`deal` FROM `warn` WHERE `device`=? AND `manager`=?;"
	err := utils.Mysql.QueryRow(SQL, row.Device, row.Manager).Scan(&row.Id, &row.Time, &row.Deal)
	return err
}

type warnQueryRes struct {
	Id     uint32         `json:"id"`
	Device string         `json:"device"`
	Time   utils.TimeType `json:"time"`
	Deal   bool           `json:"deal"`
	Addr   string         `json:"addr"`

	Old [4]WineWithIdNameRemain `json:"old"`
}

func WarnQuery(manager, cond string, deal, limit, page int) (int, []warnQueryRes, error) {
	SQL := "none"
	if deal == -1 {
		SQL = utils.Format(
			"SELECT COUNT(w.`id`) AS `total` FROM `warn` AS w LEFT JOIN `device` AS d ON w.`device`=d.`id` "+
				"WHERE w.`manager`='%s' AND (w.`device` LIKE '%%%s%%' OR d.`addr` LIKE '%%%s%%');", manager, cond, cond,
		)
	} else {
		SQL = utils.Format(
			"SELECT COUNT(w.`id`) AS `total` FROM `warn` AS w LEFT JOIN `device` AS d ON w.`device`=d.`id` "+
				"WHERE w.`manager`='%s' AND w.`deal`=%d AND (w.`device` LIKE '%%%s%%' OR d.`addr` LIKE '%%%s%%');",
			manager, deal, cond, cond,
		)
	}
	total := 0
	err := utils.Mysql.QueryRow(SQL).Scan(&total)
	if err != nil {
		return 0, nil, err
	}
	if total == 0 {
		return 0, []warnQueryRes{}, err
	}
	if deal == -1 {
		SQL = utils.Format(
			"SELECT w.`id`,w.`device`,w.`time`,w.`deal`,d.`addr`,d.`wine1`,w1.`name`,d.`remain1`,"+
				"d.`wine2`,w2.`name`,d.`remain2`,d.`wine3`,w3.`name`,d.`remain3`,d.`wine4`,w4.`name`,d.`remain4` "+
				"FROM `warn` AS w LEFT JOIN `device` AS d ON w.`device`=d.`id` "+
				"INNER JOIN `wine` AS w1 ON d.`wine1`=w1.`id` INNER JOIN `wine` AS w2 ON d.`wine1`=w2.`id` "+
				"INNER JOIN `wine` AS w3 ON d.`wine1`=w3.`id` INNER JOIN `wine` AS w4 ON d.`wine1`=w4.`id` "+
				"WHERE w.`manager`='%s' AND (w.`device` LIKE '%%%s%%' OR d.`addr` LIKE '%%%s%%') LIMIT %d OFFSET %d;",
			manager, cond, cond, limit, (page-1)*limit,
		)
	} else {
		SQL = utils.Format(
			"SELECT w.`id`,w.`device`,w.`time`,w.`deal`,d.`addr`,d.`wine1`,w1.`name`,d.`remain1`,"+
				"d.`wine2`,w2.`name`,d.`remain2`,d.`wine3`,w3.`name`,d.`remain3`,d.`wine4`,w4.`name`,d.`remain4` "+
				"FROM `warn` AS w LEFT JOIN `device` AS d ON w.`device`=d.`id` "+
				"INNER JOIN `wine` AS w1 ON d.`wine1`=w1.`id` INNER JOIN `wine` AS w2 ON d.`wine1`=w2.`id` "+
				"INNER JOIN `wine` AS w3 ON d.`wine1`=w3.`id` INNER JOIN `wine` AS w4 ON d.`wine1`=w4.`id` "+
				"WHERE w.`manager`='%s' AND w.`deal`=%d AND (w.`device` LIKE '%%%s%%' OR d.`addr` LIKE '%%%s%%') "+
				"LIMIT %d OFFSET %d;", manager, deal, cond, cond, limit, (page-1)*limit,
		)
	}
	var rows *sql.Rows
	res := make([]warnQueryRes, 0)
	rows, err = utils.Mysql.Query(SQL)
	if err != nil {
		return 0, nil, err
	}
	for rows.Next() {
		var tmp warnQueryRes
		_ = rows.Scan(
			&tmp.Id, &tmp.Device, &tmp.Time, &tmp.Deal, &tmp.Addr,
			&tmp.Old[0].Id, &tmp.Old[0].Name, &tmp.Old[0].Remain,
			&tmp.Old[1].Id, &tmp.Old[1].Name, &tmp.Old[1].Remain,
			&tmp.Old[2].Id, &tmp.Old[2].Name, &tmp.Old[2].Remain,
			&tmp.Old[3].Id, &tmp.Old[3].Name, &tmp.Old[3].Remain,
		)
		res = append(res, tmp)
	}
	_ = rows.Close()
	return total, res, nil
}