wine-server/utils/tables/trade.go

package tables

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

type TradeTable struct {
	Id      string
	Trade   string
	Device  string
	Time    utils.TimeType
	Payer   string
	Wine    uint16
	Price   uint16
	Weight  uint16
	Cash    uint32
	Manager string
}

func CreateTradeTable() error {
	SQL := "CREATE TABLE IF NOT EXISTS `trade`(" +
		"`id` VARCHAR(32) PRIMARY KEY NOT NULL," +
		"`trade` VARCHAR(64) NOT NULL," +
		"`device` VARCHAR(32) NOT NULL," +
		"`time` DATETIME DEFAULT CURRENT_TIMESTAMP," +
		"`payer` VARCHAR(64) NOT NULL," +
		"`wine` SMALLINT UNSIGNED NOT NULL," +
		"`price` SMALLINT UNSIGNED NOT NULL," +
		"`weight` SMALLINT UNSIGNED NOT NULL," +
		"`cash` INT UNSIGNED NOT NULL," +
		"`manager` VARCHAR(16));"
	_, err := utils.Mysql.Exec(SQL)
	return err
}

func (row *TradeTable) Insert() error {
	SQL := "INSERT INTO `trade`(`id`,`trade`,`device`,`payer`,`wine`,`price`,`weight`,`cash`,`manager`) VALUES(?,?,?,?,?,?,?,?,?);"
	pre, err := utils.Mysql.Prepare(SQL)
	if err != nil {
		return err
	}
	_, err = pre.Exec(row.Id, row.Trade, row.Device, row.Payer, row.Wine, row.Price, row.Weight, row.Cash, row.Manager)
	return err
}

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

func (row *TradeTable) Update(args utils.JsonType) error {
	keys, values := utils.UnZip(args)
	SQL := utils.Format("UPDATE `trade` 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 *TradeTable) UpdateSelf() error {
	SQL := "UPDATE `trade` SET `trade`=?,`device`=?,`time`=?,`payer`=?,`wine`=?`price`=?,`weight`=?,`cash`=?,`manager`=? WHERE `id`=?;"
	pre, err := utils.Mysql.Prepare(SQL)
	if err != nil {
		return err
	}
	_, err = pre.Exec(row.Trade, row.Device, row.Time, row.Payer, row.Wine, row.Price, row.Weight, row.Cash, row.Manager, row.Id)
	return err
}

func (row *TradeTable) Get() error {
	SQL := "SELECT `trade`,`device`,`time`,`payer`,`wine`,`price`,`weight`,`cash`,`manager` FROM `trade` WHERE `id`=?;"
	pre, err := utils.Mysql.Prepare(SQL)
	if err != nil {
		return err
	}
	err = pre.QueryRow(row.Id).Scan(
		&row.Trade, &row.Device, &row.Time, &row.Payer,
		&row.Wine, &row.Price, &row.Weight, &row.Cash, &row.Manager,
	)
	return err
}

func TradeQuery(cond, uid string, limit, page int) (int, []utils.JsonType, error) {
	like := utils.Format("%%%s%%", cond)
	SQL := "SELECT COUNT(t.`id`) FROM `trade` AS t LEFT JOIN `wine` AS w ON t.`wine`=w.`id` WHERE t.`payer`=? AND (t.`id` LIKE ? OR w.`name` LIKE ?);"
	pre, err := utils.Mysql.Prepare(SQL)
	if err != nil {
		return 0, nil, err
	}
	total := 0
	err = pre.QueryRow(uid, like, like).Scan(&total)
	if err != nil {
		return 0, nil, err
	}
	if total == 0 {
		return 0, []utils.JsonType{}, nil
	}
	SQL = "SELECT t.`id`,t.`device`,t.`time`,w.`name`,t.`price`,t.`weight`,t.`cash`,r.`cash`,r.`reason` " +
		"FROM `trade` AS t LEFT JOIN `wine` AS w ON t.`wine`=w.`id` LEFT JOIN `refund` AS r ON t.`id`=r.`tid` " +
		"WHERE t.`payer`=? AND (t.`id` LIKE ? OR w.`name` LIKE ?) ORDER BY t.`time` DESC LIMIT ? OFFSET ?;"
	pre, err = utils.Mysql.Prepare(SQL)
	if err != nil {
		return 0, nil, err
	}
	var rows *sql.Rows
	rows, err = pre.Query(uid, like, like, limit, (page-1)*limit)
	if err != nil {
		return 0, nil, err
	}
	res := make([]utils.JsonType, 0)
	for rows.Next() {
		var one TradeTable
		refund := 0
		wineName, reason := "", ""
		_ = rows.Scan(&one.Id, &one.Device, &one.Time, &wineName, &one.Price, &one.Weight, &one.Cash, &refund, &reason)
		res = append(res, utils.JsonType{
			"id": one.Id, "device": one.Device, "time": one.Time, "wine": wineName, "price": one.Price,
			"weight": one.Weight, "cash": one.Cash, "refund": refund, "reason": reason,
		})
	}
	_ = rows.Close()
	return total, res, nil
}