wine-server/utils/tables/user.go

package tables

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

type UserTable struct {
	Id    string
	First utils.TimeType
	Last  utils.TimeType

	Vip         bool
	Cash        uint64
	MaxCost     uint32
	BuyCount    uint32
	BuyCost     uint64
	RefundCount uint32
	RefundCost  uint64
}

func CreateUserTable() error {
	SQL := "CREATE TABLE IF NOT EXISTS `user`(" +
		"`id` VARCHAR(64) PRIMARY KEY NOT NULL," +
		"`first` DATETIME DEFAULT CURRENT_TIMESTAMP," +
		"`last` DATETIME DEFAULT CURRENT_TIMESTAMP," +
		"`vip` BOOL DEFAULT FALSE," +
		"`cash` BIGINT UNSIGNED DEFAULT 0," +
		"`max_cost` INT UNSIGNED DEFAULT 0," +
		"`buy_count` INT UNSIGNED DEFAULT 0," +
		"`buy_cost` BIGINT UNSIGNED DEFAULT 0," +
		"`refund_count` INT UNSIGNED DEFAULT 0," +
		"`refund_cost` BIGINT UNSIGNED DEFAULT 0);"
	_, err := utils.Mysql.Exec(SQL)
	return err
}

func (row *UserTable) Insert() error {
	SQL := "INSERT INTO `user` (`id`,`max_cost`,`buy_count`,`buy_cost`) VALUES (?,?,?,?,?);"
	pre, err := utils.Mysql.Prepare(SQL)
	if err != nil {
		return err
	}
	_, err = pre.Exec(row.Id, row.MaxCost, row.BuyCount, row.BuyCost)
	return err
}

// Delete deprecated
func (row *UserTable) Delete() error {
	SQL := utils.Format("DELETE FROM `user` WHERE `id`='%s';", row.Id)
	_, err := utils.Mysql.Exec(SQL)
	return err
}

func (row *UserTable) Update(args utils.JsonType) error {
	keys, values := utils.UnZip(args)
	SQL := utils.Format("UPDATE `user` 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 *UserTable) UpdateSelf() error {
	SQL := "UPDATE `user` SET `last`=?,`vip`=?,`cash`=?,`max_cost`=?,`buy_count`=?,`buy_cost`=?,`refund_count`=?,`refund_cost`=? WHERE `id`=?;"
	pre, err := utils.Mysql.Prepare(SQL)
	if err != nil {
		return err
	}
	_, err = pre.Exec(row.Last, row.Vip, row.Cash, row.MaxCost, row.BuyCount, row.BuyCost, row.RefundCount, row.RefundCost, row.Id)
	return err
}

func (row *UserTable) Get() error {
	SQL := "SELECT `first`,`last`,`vip`,`cash`,`max_cost`,`buy_count`,`buy_cost`,`refund_count`,`refund_cost` FROM `user` WHERE `id`=?;"
	pre, err := utils.Mysql.Prepare(SQL)
	if err != nil {
		return err
	}
	err = pre.QueryRow(row.Id).Scan(
		&row.First, &row.Last, &row.Vip, &row.Cash, &row.MaxCost,
		&row.BuyCount, &row.BuyCost, &row.RefundCount, &row.RefundCost,
	)
	return err
}

func UserQuery(id string, vip, limit, page int) (int, []utils.JsonType, error) {
	SQL := "none"
	if vip == -1 {
		SQL = utils.Format("SELECT COUNT(`id`) AS `total` FROM `user` WHERE `id` LIKE '%%%s%%';", id)
	} else {
		SQL = utils.Format(
			"SELECT COUNT(`id`) AS `total` FROM `user` WHERE `vip`=%d AND `id` LIKE '%%%s%%';", vip, id,
		)
	}
	total := 0
	err := utils.Mysql.QueryRow(SQL).Scan(&total)
	if err != nil {
		return 0, nil, err
	}
	if total == 0 {
		return 0, []utils.JsonType{}, err
	}
	if vip == -1 {
		SQL = utils.Format(
			"SELECT `id`,`first`,`last`,`vip`,`cash`,`max_cost`,`buy_count`,`buy_cost`,`refund_count`,`refund_cost` "+
				"FROM `user` WHERE `id` LIKE '%%%s%%' LIMIT %d OFFSET %d;", id, limit, (page-1)*limit,
		)
	} else {
		SQL = utils.Format(
			"SELECT `id`,`first`,`last`,`vip`,`cash`,`max_cost`,`buy_count`,`buy_cost`,`refund_count`,`refund_cost` "+
				"FROM `user` WHERE `vip`=%d AND `id` LIKE '%%%s%%' LIMIT %d OFFSET %d;", vip, id, limit, (page-1)*limit,
		)
	}
	var rows *sql.Rows
	res := make([]utils.JsonType, 0)
	rows, err = utils.Mysql.Query(SQL)
	if err != nil {
		return 0, nil, err
	}
	for rows.Next() {
		var tmp UserTable
		err = rows.Scan(
			&tmp.Id, &tmp.First, &tmp.Last, &tmp.Vip, &tmp.Cash, &tmp.MaxCost,
			&tmp.BuyCount, &tmp.BuyCost, &tmp.RefundCount, &tmp.RefundCost,
		)
		res = append(res, utils.JsonType{
			"id": tmp.Id, "first": tmp.First, "last": tmp.Last, "vip": tmp.Vip, "cash": tmp.Cash,
			"max_cost": tmp.MaxCost, "buy_count": tmp.BuyCount, "buy_cost": tmp.BuyCost,
			"refund_count": tmp.RefundCount, "refund_cost": tmp.RefundCost,
		})
	}
	_ = rows.Close()
	return total, res, nil
}

func UserQueryForAdmin(manager, id string, vip, limit, page int) (int, []utils.JsonType, error) {
	SQL := "none"
	if vip == -1 {
		SQL = utils.Format(
			"SELECT COUNT(u.`id`) FROM `user` AS u WHERE u.`id` LIKE '%%%s%%' AND u.`id` IN "+
				"(SELECT DISTINCT t.`payer` FROM `trade` AS t WHERE t.`manager`='%s');",
			id, manager,
		)
	} else {
		SQL = utils.Format(
			"SELECT COUNT(u.`id`) FROM `user` AS u WHERE u.`vip`=%d AND u.`id` LIKE '%%%s%%' "+
				"AND u.`id` IN (SELECT DISTINCT t.`payer` FROM `trade` AS t WHERE t.`manager`='%s'); ",
			vip, id, manager,
		)
	}
	total := 0
	err := utils.Mysql.QueryRow(SQL).Scan(&total)
	if err != nil {
		return 0, nil, err
	}
	if total == 0 {
		return 0, []utils.JsonType{}, err
	}
	if vip == -1 {
		SQL = utils.Format(
			"SELECT u.`id`,u.`first`,u.`last`,u.`vip`,u.`cash`,u.`max_cost`,u.`buy_count`,u.`buy_cost`,"+
				"u.`refund_count`,u.`refund_cost` FROM `user` AS u WHERE u.`id` LIKE '%%%s%%' AND u.`id` "+
				"IN (SELECT DISTINCT t.`payer` FROM `trade` AS t WHERE t.`manager`='%s') LIMIT %d OFFSET %d;",
			id, manager, limit, (page-1)*limit,
		)
	} else {
		SQL = utils.Format(
			"SELECT u.`id`,u.`first`,u.`last`,u.`vip`,u.`cash`,u.`max_cost`,u.`buy_count`,u.`buy_cost`,"+
				"u.`refund_count`,u.`refund_cost` FROM `user` AS u WHERE u.`vip`=%d AND u.`id` LIKE '%%%s%%' AND "+
				"u.`id` IN (SELECT t.`payer` FROM `trade` AS t WHERE t.`manager`='%s') LIMIT %d OFFSET %d;",
			vip, id, manager, limit, (page-1)*limit,
		)
	}
	var rows *sql.Rows
	res := make([]utils.JsonType, 0)
	rows, err = utils.Mysql.Query(SQL)
	if err != nil {
		return 0, nil, err
	}
	for rows.Next() {
		var tmp UserTable
		err = rows.Scan(
			&tmp.Id, &tmp.First, &tmp.Last, &tmp.Vip, &tmp.Cash, &tmp.MaxCost,
			&tmp.BuyCount, &tmp.BuyCost, &tmp.RefundCount, &tmp.RefundCost,
		)
		res = append(res, utils.JsonType{
			"id": tmp.Id, "first": tmp.First, "last": tmp.Last, "vip": tmp.Vip, "cash": tmp.Cash,
			"max_cost": tmp.MaxCost, "buy_count": tmp.BuyCount, "buy_cost": tmp.BuyCost,
			"refund_count": tmp.RefundCount, "refund_cost": tmp.RefundCost,
		})
	}
	_ = rows.Close()
	return total, res, nil
}