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

package tables

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

type ManagerTable struct {
	Id    string
	Super bool
	First utils.TimeType
	Last  utils.TimeType

	Name     string
	Account  string
	Password string

	Order  uint32
	Income uint64
}

func CreateManagerTable() error {
	SQL := "CREATE TABLE IF NOT EXISTS `manager`(" +
		"`id` VARCHAR(16) PRIMARY KEY," +
		"`super` BOOL DEFAULT FALSE," +
		"`first` DATETIME DEFAULT CURRENT_TIMESTAMP," +
		"`last` DATETIME DEFAULT CURRENT_TIMESTAMP," +
		"`name` VARCHAR(16) NOT NULL," +
		"`account` VARCHAR(32) NOT NULL," +
		"`password` CHAR(128) NOT NULL," +
		"`order` INT UNSIGNED DEFAULT 0," +
		"`income` BIGINT UNSIGNED DEFAULT 0);"
	_, err := utils.Mysql.Exec(SQL)
	return err
}

func (row *ManagerTable) Insert() error {
	SQL := "INSERT INTO `manager`(`id`,`name`,`account`,`password`) VALUES(?,?,?,?);"
	pre, err := utils.Mysql.Prepare(SQL)
	if err != nil {
		return err
	}
	_, err = pre.Exec(row.Id, row.Name, row.Account, row.Password)
	return err
}

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

func (row *ManagerTable) Update(args utils.JsonType) error {
	keys, values := utils.UnZip(args)
	SQL := utils.Format("UPDATE `manager` 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 *ManagerTable) UpdateSelf() error {
	SQL := "UPDATE `manager` SET `super`=?,`last`=?,`name`=?,`account`=?,`password`=?,`order`=?,`income`=? WHERE `id`=?;"
	pre, err := utils.Mysql.Prepare(SQL)
	if err != nil {
		return err
	}
	row.Last = utils.TimeNow()
	_, err = pre.Exec(row.Super, row.Last, row.Name, row.Account, row.Password, row.Order, row.Income, row.Id)
	return err
}

func (row *ManagerTable) Get() error {
	SQL := "SELECT `super`,`first`,`last`,`name`,`account`,`password`,`order`,`income` FROM `manager` WHERE `id`=?;"
	pre, err := utils.Mysql.Prepare(SQL)
	if err != nil {
		return err
	}
	err = pre.QueryRow(row.Id).Scan(
		&row.Super, &row.First, &row.Last, &row.Name,
		&row.Account, &row.Password, &row.Order, &row.Income,
	)
	return err
}

// GetByAcc change to `Login(acc, pwd) error`
func (row *ManagerTable) GetByAcc() error {
	SQL := "SELECT `id`,`super`,`first`,`last`,`name`,`password`,`order`,`income` FROM `manager` WHERE `account`=? LIMIT 1;"
	pre, err := utils.Mysql.Prepare(SQL)
	if err != nil {
		return err
	}
	err = pre.QueryRow(row.Account).Scan(
		&row.Id, &row.Super, &row.First, &row.Last,
		&row.Name, &row.Password, &row.Order, &row.Income,
	)
	return err
}

func (row *ManagerTable) Login() error {
	SQL := "SELECT `id`,`super`,`first`,`last`,`name`,`order`,`income` FROM `manager` WHERE `account`=? AND `password`=?;"
	return utils.Mysql.QueryRow(SQL, row.Account, row.Password).Scan(
		&row.Id, &row.Super, &row.First, &row.Last,
		&row.Name, &row.Order, &row.Income,
	)
}

func ManagerQuery(name string) ([]utils.JsonType, error) {
	like := utils.Format("%%%s%%", name)
	SQL := "SELECT `id`,`name` FROM `manager` WHERE `super`=0 AND `name` LIKE ? LIMIT 10;"
	pre, err := utils.Mysql.Prepare(SQL)
	if err != nil {
		return nil, err
	}
	rows, err := pre.Query(like)
	if err != nil {
		return nil, err
	}
	res := make([]utils.JsonType, 0)
	for rows.Next() {
		tid, tna := "", ""
		_ = rows.Scan(&tid, &tna)
		res = append(res, utils.JsonType{"id": tid, "name": tna})
	}
	_ = rows.Close()
	return res, nil
}

func ManagerQueryForAdmin(cond string, limit, page int) (int, []utils.JsonType, error) {
	SQL := utils.Format(
		"SELECT COUNT(`id`) AS `total` FROM `manager` WHERE `super`=0 AND (`name` LIKE '%%%s%%' OR `account` LIKE '%%%s%%');",
		cond, cond,
	)
	total := 0
	err := utils.Mysql.QueryRow(SQL).Scan(&total)
	if err != nil {
		return 0, nil, err
	}
	if total == 0 {
		return 0, []utils.JsonType{}, nil
	}
	SQL = utils.Format(
		"SELECT `id`,`first`,`last`,`name`,`account`,`order`,`income` FROM `manager` "+
			"WHERE `super`=0 AND (`name` LIKE '%%%s%%' OR `account` LIKE '%%%s%%') LIMIT %d OFFSET %d;",
		cond, cond, 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 one ManagerTable
		_ = rows.Scan(&one.Id, &one.First, &one.Last, &one.Name, &one.Account, &one.Order, &one.Income)
		res = append(res, utils.JsonType{
			"id": one.Id, "name": one.Name, "first": one.First, "last": one.Last,
			"account": one.Account, "order": one.Order, "income": one.Income,
		})
	}
	return total, res, nil
}

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