wine-server/utils/tables/worker.go

package tables

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

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

	Name     string
	Phone    string
	Password string
	Manager  string

	Count uint32
	Todo  uint32
	Avg   uint32
	Max   uint32
}

func CreateWorkerTable() error {
	SQL := "CREATE TABLE IF NOT EXISTS `worker`(" +
		"`id` VARCHAR(16) PRIMARY KEY NOT NULL," +
		"`first` DATETIME DEFAULT CURRENT_TIMESTAMP," +
		"`last` DATETIME DEFAULT CURRENT_TIMESTAMP," +
		"`name` VARCHAR(16) NOT NULL," +
		"`phone` VARCHAR(11) NOT NULL," +
		"`password` VARCHAR(128) NOT NULL," +
		"`manager` VARCHAR(16) DEFAULT ''," +
		"`count` INT UNSIGNED DEFAULT 0," +
		"`todo` INT UNSIGNED DEFAULT 0," +
		"`avg_cost` INT UNSIGNED DEFAULT 0," +
		"`max_cost` INT UNSIGNED DEFAULT 0);"
	_, err := utils.Mysql.Exec(SQL)
	return err
}

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

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

func (row *WorkerTable) Update(args utils.JsonType) error {
	keys, values := utils.UnZip(args)
	SQL := utils.Format("UPDATE `worker` 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 *WorkerTable) UpdateSelf() error {
	SQL := "UPDATE `worker` SET `last`=?,`name`=?,`phone`=?,`password`=?,`manager`=?," +
		"`count`=?,`todo`=?,`avg_cost`=?,`max_cost`=? WHERE `id`=?;"
	row.Last = utils.TimeNow()
	_, err := utils.Mysql.Exec(
		SQL, row.Last, row.Name, row.Phone, row.Password, row.Manager,
		row.Count, row.Todo, row.Avg, row.Max, row.Id,
	)
	return err
}

func (row *WorkerTable) Get() error {
	SQL := "SELECT `first`,`last`,`name`,`phone`,`password`,`manager`,`count`,`todo`," +
		"`avg_cost`,`max_cost` FROM `worker` WHERE `id`=?;"
	return utils.Mysql.QueryRow(SQL, row.Id).Scan(
		&row.First, &row.Last, &row.Name, &row.Phone, &row.Password,
		&row.Manager, &row.Count, &row.Todo, &row.Avg, &row.Max,
	)
}

func (row *WorkerTable) GetByPhone() error {
	SQL := "SELECT `id`,`first`,`last`,`name`,`password`,`manager`,`count`,`todo`," +
		"`avg_cost`,`max_cost` FROM `worker` WHERE `phone`=?;"
	return utils.Mysql.QueryRow(SQL, row.Phone).Scan(
		&row.Id, &row.First, &row.Last, &row.Name, &row.Password,
		&row.Manager, &row.Count, &row.Todo, &row.Avg, &row.Max,
	)
}

type workerQueryRes struct {
	Id    string `json:"id"`
	Name  string `json:"name"`
	Phone string `json:"phone"`
	Count uint32 `json:"count"`
	Cost  uint32 `json:"cost"`

	First utils.TimeType `json:"first"`
	Last  utils.TimeType `json:"last"`

	Manager struct {
		Id   string `json:"id"`
		Name string `json:"name"`
	} `json:"manager"`
}

func WorkerQuery(manager, cond string, limit, page int) (int, []workerQueryRes, error) {
	SQL := "none"
	if manager == "" { // any
		SQL = utils.Format(
			"SELECT COUNT(`id`) AS `total` FROM `worker` WHERE `name` LIKE '%%%s%%' OR `phone` LIKE '%%%s%%';",
			cond, cond,
		)
	} else { // one
		SQL = utils.Format(
			"SELECT COUNT(`id`) AS `total` FROM `worker` WHERE `manager`='%s' AND (`name` LIKE '%%%s%%' OR `phone` LIKE '%%%s%%');",
			manager, cond, cond,
		)
	}
	total := 0
	err := utils.Mysql.QueryRow(SQL).Scan(&total)
	if err != nil {
		return 0, nil, err
	}
	if total == 0 {
		return 0, []workerQueryRes{}, err
	}
	if manager == "" { // any
		SQL = utils.Format(
			"SELECT w.`id`,w.`first`,w.`last`,w.`name`,w.`phone`,w.`count`,w.`avg_cost`,m.`id`,m.`name` "+
				"FROM `worker` AS w LEFT JOIN `manager` AS m ON w.`manager`=m.`id` "+
				"WHERE w.`name` LIKE '%%%s%%' OR w.`phone` LIKE '%%%s%%' LIMIT %d OFFSET %d;",
			cond, cond, limit, (page-1)*limit,
		)
	} else { // one
		SQL = utils.Format(
			"SELECT w.`id`,w.`first`,w.`last`,w.`name`,w.`phone`,w.`count`,w.`avg_cost`,m.`id`,m.`name` "+
				"FROM `worker` AS w LEFT JOIN `manager` AS m ON w.`manager`=m.`id` "+
				"WHERE w.`manager`='%s' AND (w.`name` LIKE '%%%s%%' OR w.`phone` LIKE '%%%s%%') LIMIT %d OFFSET %d;",
			manager, cond, cond, limit, (page-1)*limit,
		)
	}

	var rows *sql.Rows
	rows, err = utils.Mysql.Query(SQL)
	if err != nil {
		return 0, nil, err
	}
	res := make([]workerQueryRes, 0)
	for rows.Next() {
		var one workerQueryRes
		_ = rows.Scan(
			&one.Id, &one.First, &one.Last, &one.Name, &one.Phone,
			&one.Count, &one.Cost, &one.Manager.Id, &one.Manager.Name,
		)
		res = append(res, one)
	}
	_ = rows.Close()
	return total, res, nil
}

type workerQueryForAdminRes struct {
	Id    string `json:"id"`
	Name  string `json:"name"`
	Phone string `json:"phone"`
	Count uint32 `json:"count"`
	Cost  uint32 `json:"cost"`

	First utils.TimeType `json:"first"`
	Last  utils.TimeType `json:"last"`
}

func WorkerQueryForAdmin(manager, cond string, limit, page int) (int, []workerQueryForAdminRes, error) {
	SQL := utils.Format(
		"SELECT COUNT(`id`) AS `total` FROM `worker` WHERE `manager`='%s' AND (`name` LIKE '%%%s%%' OR `phone` LIKE '%%%s%%');",
		manager, cond, cond,
	)
	total := 0
	err := utils.Mysql.QueryRow(SQL).Scan(&total)
	if err != nil {
		return 0, nil, err
	}
	if total == 0 {
		return 0, []workerQueryForAdminRes{}, err
	}
	SQL = utils.Format(
		"SELECT `id`,`first`,`last`,`name`,`phone`,`count`,`avg_cost` FROM `worker` "+
			"WHERE `manager`='%s' AND (`name` LIKE '%%%s%%' OR `phone` LIKE '%%%s%%') LIMIT %d OFFSET %d;",
		manager, cond, cond, limit, (page-1)*limit,
	)
	var rows *sql.Rows
	rows, err = utils.Mysql.Query(SQL)
	if err != nil {
		return 0, nil, err
	}
	res := make([]workerQueryForAdminRes, 0)
	for rows.Next() {
		var one workerQueryForAdminRes
		_ = rows.Scan(&one.Id, &one.First, &one.Last, &one.Name, &one.Phone, &one.Count, &one.Cost)
		res = append(res, one)
	}
	_ = rows.Close()
	return total, res, nil
}

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

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

type workerQueryForHistoryRes struct {
	Id    string `json:"id"`
	Name  string `json:"name"`
	Phone string `json:"phone"`
	Count uint32 `json:"count"`
	Todo  uint32 `json:"todo"`
	Avg   uint32 `json:"avg"`
	Max   uint32 `json:"max"`

	Last utils.TimeType `json:"last"`
}

func WorkerQueryForHistory(manager, cond string, limit, page int) (int, []workerQueryForHistoryRes, error) {
	SQL := utils.Format(
		"SELECT COUNT(`id`) AS `total` FROM `worker` WHERE `manager`='%s' AND (`name` LIKE '%%%s%%' OR `phone` LIKE '%%%s%%');",
		manager, cond, cond,
	)
	total := 0
	err := utils.Mysql.QueryRow(SQL).Scan(&total)
	if err != nil {
		return 0, nil, err
	}
	if total == 0 {
		return 0, []workerQueryForHistoryRes{}, err
	}
	SQL = utils.Format(
		"SELECT `id`,`last`,`name`,`phone`,`count`,`todo`,`avg_cost`,`max_cost` FROM `worker` "+
			"WHERE `manager`='%s' AND (`name` LIKE '%%%s%%' OR `phone` LIKE '%%%s%%') LIMIT %d OFFSET %d;",
		manager, cond, cond, limit, (page-1)*limit,
	)
	var rows *sql.Rows
	rows, err = utils.Mysql.Query(SQL)
	if err != nil {
		return 0, nil, err
	}
	res := make([]workerQueryForHistoryRes, 0)
	for rows.Next() {
		var one workerQueryForHistoryRes
		_ = rows.Scan(
			&one.Id, &one.Last, &one.Name, &one.Phone,
			&one.Count, &one.Todo, &one.Avg, &one.Max,
		)
		res = append(res, one)
	}
	_ = rows.Close()
	return total, res, nil
}