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
}

func TradeQueryForAdmin(manager, 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.`manager`=? AND 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(manager, 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.`manager`=? AND 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(manager, 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
}

func TradeRankAdmins(start, end string) ([]idNameTotal, error) {
	SQL := "SELECT m.`id`,m.`name`,SUM(t.`cash`) AS `income` FROM `trade` AS t " +
		"LEFT JOIN `manager` AS m ON t.`manager`=m.`id` " +
		"WHERE t.`time` BETWEEN TIMESTAMP(?) AND TIMESTAMP(?) GROUP BY t.`manager` ORDER BY `income` DESC;"
	rows, err := utils.Mysql.Query(SQL, start, end)
	if err != nil {
		return nil, err
	}
	res := make([]idNameTotal, 0)
	for rows.Next() {
		var one idNameTotal
		_ = rows.Scan(&one.Id, &one.Name, &one.Total)
		res = append(res, one)
	}
	_ = rows.Close()
	return res, nil
}

func TradeRankDevices(start, end string) ([]idTotal, error) {
	SQL := "SELECT `device`,SUM(`cash`) AS `income` FROM `trade` " +
		"WHERE `time` BETWEEN TIMESTAMP(?) AND TIMESTAMP(?) GROUP BY `device` ORDER BY `income` DESC;"
	rows, err := utils.Mysql.Query(SQL, start, end)
	if err != nil {
		return nil, err
	}
	res := make([]idTotal, 0)
	for rows.Next() {
		var one idTotal
		_ = rows.Scan(&one.Id, &one.Total)
		res = append(res, one)
	}
	_ = rows.Close()
	return res, nil
}

func TradeRankDevicesForAdmin(manager, start, end string) ([]idTotal, error) {
	SQL := "SELECT `device`,SUM(`cash`) AS `income` FROM `trade` WHERE `manager`=? " +
		"AND (`time` BETWEEN TIMESTAMP(?) AND TIMESTAMP(?)) GROUP BY `device` ORDER BY `income` DESC;"
	rows, err := utils.Mysql.Query(SQL, manager, start, end)
	if err != nil {
		return nil, err
	}
	res := make([]idTotal, 0)
	for rows.Next() {
		var one idTotal
		_ = rows.Scan(&one.Id, &one.Total)
		res = append(res, one)
	}
	_ = rows.Close()
	return res, nil
}

func TradeRankWines(start, end string) ([]idNameTotal, error) {
	SQL := "SELECT w.`id`,w.`name`,SUM(t.`cash`) AS `income` FROM `trade` AS t " +
		"LEFT JOIN `wine` AS w ON t.`wine`=w.`id` " +
		"WHERE t.`time` BETWEEN TIMESTAMP(?) AND TIMESTAMP(?) GROUP BY t.`wine` ORDER BY `income` DESC;"
	rows, err := utils.Mysql.Query(SQL, start, end)
	if err != nil {
		return nil, err
	}
	res := make([]idNameTotal, 0)
	for rows.Next() {
		var one idNameTotal
		_ = rows.Scan(&one.Id, &one.Name, &one.Total)
		res = append(res, one)
	}
	_ = rows.Close()
	return res, nil
}

func TradeRankWinesForAdmin(manager, start, end string) ([]idNameTotal, error) {
	SQL := "SELECT w.`id`,w.`name`,SUM(t.`cash`) AS `income` FROM `trade` AS t " +
		"LEFT JOIN `wine` AS w ON t.`wine`=w.`id` WHERE t.`manager`=? " +
		"AND (t.`time` BETWEEN TIMESTAMP(?) AND TIMESTAMP(?)) GROUP BY t.`wine` ORDER BY `income` DESC;"
	rows, err := utils.Mysql.Query(SQL, manager, start, end)
	if err != nil {
		return nil, err
	}
	res := make([]idNameTotal, 0)
	for rows.Next() {
		var one idNameTotal
		_ = rows.Scan(&one.Id, &one.Name, &one.Total)
		res = append(res, one)
	}
	_ = rows.Close()
	return res, nil
}

func TradeQuerySpecificOne(filed, value string, kind uint8) ([]timeTotal, error) {
	SQL := "none"
	if kind == 0 {
		SQL = utils.Format(
			"SELECT DATE_FORMAT(`time`,'%%Y-%%m') as `tt`,SUM(`cash`) FROM `trade` WHERE `%s`=? GROUP BY `tt`;", filed,
		)
	} else {
		align, days := getAlignDays(kind)
		SQL = utils.Format(
			"SELECT DATE_FORMAT(`time`,'%s') as `tt`,SUM(`cash`) FROM `trade` WHERE `%s`=? AND (`time` BETWEEN "+
				"DATE_SUB(CURRENT_TIMESTAMP,INTERVAL %d DAY) AND CURRENT_TIMESTAMP) GROUP BY `tt`;", align, filed, days,
		)
	}
	rows, err := utils.Mysql.Query(SQL, value)
	if err != nil {
		return nil, err
	}
	res := make([]timeTotal, 0)
	for rows.Next() {
		var one timeTotal
		_ = rows.Scan(&one.Time, &one.Total)
		res = append(res, one)
	}
	_ = rows.Close()
	return res, nil
}

func TradeQuerySpecificOneForAdmin(manager, filed, value string, kind uint8) ([]timeTotal, error) {
	SQL := "none"
	if kind == 0 {
		SQL = utils.Format(
			"SELECT DATE_FORMAT(`time`,'%%Y-%%m') as `tt`,SUM(`cash`) FROM `trade` "+
				"WHERE `manager`=? AND `%s`=? GROUP BY `tt`;", filed,
		)
	} else {
		align, days := getAlignDays(kind)
		SQL = utils.Format(
			"SELECT DATE_FORMAT(`time`,'%s') as `tt`,SUM(`cash`) FROM `trade` WHERE `manager`=? AND "+
				"`%s`=? AND (`time` BETWEEN DATE_SUB(CURRENT_TIMESTAMP,INTERVAL %d DAY) AND CURRENT_TIMESTAMP) "+
				"GROUP BY `tt`;", align, filed, days,
		)
	}
	rows, err := utils.Mysql.Query(SQL, manager, value)
	if err != nil {
		return nil, err
	}
	res := make([]timeTotal, 0)
	for rows.Next() {
		var one timeTotal
		_ = rows.Scan(&one.Time, &one.Total)
		res = append(res, one)
	}
	_ = rows.Close()
	return res, nil
}