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

package tables

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

type ChangeTable struct {
	Id      uint32
	Wid     uint32
	Device  string
	Manager string
	Worker  string
	Code    string
	Status  uint8
	Publish utils.TimeType
	Finish  utils.TimeType
	Old     [4]WineWithIdRemain
	New     [4]WineWithIdRemain
}

func CreateChangeTable() error {
	SQL := "CREATE TABLE IF NOT EXISTS `change`(" +
		"`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL," +
		"`wid` INT UNSIGNED NOT NULL," +
		"`device` VARCHAR(32) NOT NULL,`manager` VARCHAR(16) NOT NULL," +
		"`worker` VARCHAR(16) NOT NULL,`code` VARCHAR(6) NOT NULL," +
		"`status` SMALLINT UNSIGNED DEFAULT 0," +
		"`publish` DATETIME DEFAULT CURRENT_TIMESTAMP," +
		"`finish` DATETIME DEFAULT CURRENT_TIMESTAMP," +
		"`wine1o` SMALLINT UNSIGNED DEFAULT 0," +
		"`remain1o` SMALLINT UNSIGNED DEFAULT 0," +
		"`wine2o` SMALLINT UNSIGNED DEFAULT 0," +
		"`remain2o` SMALLINT UNSIGNED DEFAULT 0," +
		"`wine3o` SMALLINT UNSIGNED DEFAULT 0," +
		"`remain3o` SMALLINT UNSIGNED DEFAULT 0," +
		"`wine4o` SMALLINT UNSIGNED DEFAULT 0," +
		"`remain4o` SMALLINT UNSIGNED DEFAULT 0," +
		"`wine1n` SMALLINT UNSIGNED DEFAULT 0," +
		"`remain1n` SMALLINT UNSIGNED DEFAULT 0," +
		"`wine2n` SMALLINT UNSIGNED DEFAULT 0," +
		"`remain2n` SMALLINT UNSIGNED DEFAULT 0," +
		"`wine3n` SMALLINT UNSIGNED DEFAULT 0," +
		"`remain3n` SMALLINT UNSIGNED DEFAULT 0," +
		"`wine4n` SMALLINT UNSIGNED DEFAULT 0," +
		"`remain4n` SMALLINT UNSIGNED DEFAULT 0);"
	_, err := utils.Mysql.Exec(SQL)
	return err
}

func (row *ChangeTable) Insert() error {
	SQL := "INSERT INTO `change` (`wid`,`device`,`manager`,`worker`,`code`,`wine1o`,`remain1o`,`wine2o`,`remain2o`," +
		"`wine3o`,`remain3o`,`wine4o`,`remain4o`,`wine1n`,`remain1n`,`wine2n`,`remain2n`,`wine3n`,`remain3n`," +
		"`wine4n`,`remain4n`) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);"
	pre, err := utils.Mysql.Prepare(SQL)
	if err != nil {
		return err
	}
	_, err = pre.Exec(
		row.Wid, row.Device, row.Manager, row.Worker, row.Code,
		row.Old[0].Id, row.Old[0].Remain, row.Old[1].Id, row.Old[1].Remain,
		row.Old[2].Id, row.Old[2].Remain, row.Old[3].Id, row.Old[3].Remain,
		row.New[0].Id, row.New[0].Remain, row.New[1].Id, row.New[1].Remain,
		row.New[2].Id, row.New[2].Remain, row.New[3].Id, row.New[3].Remain,
	)
	return err
}

func (row *ChangeTable) Delete() error {
	_, err := utils.Mysql.Exec("DELETE FROM `change` WHERE `id`=?;", row.Id)
	return err
}

func (row *ChangeTable) Update(args utils.JsonType) error {
	keys, values := utils.UnZip(args)
	SQL := utils.Format("UPDATE `change` SET %s WHERE `id`=%d;", utils.SqlFields(keys), row.Id)
	pre, err := utils.Mysql.Prepare(SQL)
	if err != nil {
		return err
	}
	_, err = pre.Exec(values...)
	return err
}

func (row *ChangeTable) UpdateSelf() error {
	SQL := "UPDATE `change` SET `wid`=?,`device`=?,`manager`=?,`worker`=?,`code`=?,`status`=?,`publish`=?,`finish`=?," +
		"`wine1o`=?,`remain1o`=?,`wine2o`=?,`remain2o`=?,`wine3o`=?,`remain3o`=?,`wine4o`=?,`remain4o`=?," +
		"`wine1n`=?,`remain1n`=?,`wine2n`=?,`remain2n`=?,`wine3n`=?,`remain3n`=?,`wine4n`=?,`remain4n`=? WHERE `id`=?;"
	_, err := utils.Mysql.Exec(
		SQL, row.Wid, row.Device, row.Manager, row.Worker,
		row.Code, row.Status, row.Publish, row.Finish,
		row.Old[0].Id, row.Old[0].Remain, row.Old[1].Id, row.Old[1].Remain,
		row.Old[2].Id, row.Old[2].Remain, row.Old[3].Id, row.Old[3].Remain,
		row.New[0].Id, row.New[0].Remain, row.New[1].Id, row.New[1].Remain,
		row.New[2].Id, row.New[2].Remain, row.New[3].Id, row.New[3].Remain, row.Id,
	)
	return err
}

func (row *ChangeTable) Get() error {
	SQL := "SELECT `wid`,`device`,`manager`,`worker`,`code`,`status`,`publish`,`finish`," +
		"`wine1o`,`remain1o`,`wine2o`,`remain2o`,`wine3o`,`remain3o`,`wine4o`,`remain4o`," +
		"`wine1n`,`remain1n`,`wine2n`,`remain2n`,`wine3n`,`remain3n`,`wine4n`,`remain4n` FROM `change` WHERE `id`=?;"
	err := utils.Mysql.QueryRow(SQL, row.Id).Scan(
		&row.Wid, &row.Device, &row.Manager, &row.Worker,
		&row.Code, &row.Status, &row.Publish, &row.Finish,
		&row.Old[0].Id, &row.Old[0].Remain, &row.Old[1].Id, &row.Old[1].Remain,
		&row.Old[2].Id, &row.Old[2].Remain, &row.Old[3].Id, &row.Old[3].Remain,
		&row.New[0].Id, &row.New[0].Remain, &row.New[1].Id, &row.New[1].Remain,
		&row.New[2].Id, &row.New[2].Remain, &row.New[3].Id, &row.New[3].Remain,
	)
	return err
}

type changeQueryRes struct {
	Id     uint32 `json:"id"`
	Device struct {
		Id   string `json:"id"`
		Addr string `json:"addr"`
	} `json:"device"`
	Code    string         `json:"code"`
	Status  uint8          `json:"status"`
	Publish utils.TimeType `json:"publish"`
	Finish  utils.TimeType `json:"finish"`

	Old [4]WineWithIdNameRemain `json:"old"`
	New [4]WineWithIdNameRemain `json:"new"`
}

func ChangesQuery(manager, worker, cond string, status, limit, page int) (int, []changeQueryRes, error) {
	SQL, like := "none", utils.Format("%%%s%%", cond)
	if status == -1 {
		SQL = utils.Format(
			"SELECT COUNT(c.`id`) FROM `change` AS c LEFT JOIN `device` AS d ON c.`device`=d.`id`"+
				"WHERE c.`manager`='%s' AND c.`worker`='%s' AND (d.`id` LIKE '%s' OR d.`addr` LIKE '%s')",
			manager, worker, like, like,
		)
	} else {
		SQL = utils.Format(
			"SELECT COUNT(c.`id`) FROM `change` AS c LEFT JOIN `device` AS d ON c.`device`=d.`id`"+
				"WHERE c.`status`=%d AND c.`manager`='%s' AND c.`worker`='%s' AND (d.`id` LIKE '%s' OR d.`addr` LIKE '%s')",
			status, manager, worker, like, like,
		)
	}
	total := 0
	err := utils.Mysql.QueryRow(SQL).Scan(&total)
	if err != nil {
		return 0, nil, err
	}
	if total == 0 {
		return 0, []changeQueryRes{}, err
	}
	if status == -1 {
		SQL = utils.Format(
			"SELECT c.`id`,d.`id`,d.`addr`,c.`code`,c.`status`,c.`publish`,c.`finish`,"+
				"w1.`id`,w1.`name`,c.`remain1o`,w2.`id`,w2.`name`,c.`remain2o`,"+
				"w3.`id`,w3.`name`,c.`remain3o`,w4.`id`,w4.`name`,c.`remain4o`,"+
				"w5.`id`,w5.`name`,c.`remain1n`,w6.`id`,w6.`name`,c.`remain2n`,"+
				"w7.`id`,w7.`name`,c.`remain3n`,w8.`id`,w8.`name`,c.`remain4n` "+
				"FROM `change` AS c LEFT JOIN `device` AS d ON c.`device`=d.`id`"+
				"INNER JOIN `wine` AS w1 ON w1.`id`=c.`wine1o` INNER JOIN `wine` AS w2 ON w2.`id`=c.`wine2o` "+
				"INNER JOIN `wine` AS w3 ON w3.`id`=c.`wine3o` INNER JOIN `wine` AS w4 ON w4.`id`=c.`wine4o` "+
				"INNER JOIN `wine` AS w5 ON w5.`id`=c.`wine1n` INNER JOIN `wine` AS w6 ON w6.`id`=c.`wine2n` "+
				"INNER JOIN `wine` AS w7 ON w7.`id`=c.`wine3n` INNER JOIN `wine` AS w8 ON w8.`id`=c.`wine4n` "+
				"WHERE c.`manager`='%s' AND c.`worker`='%s' AND (d.`id` LIKE '%s' OR d.`addr` LIKE '%s') "+
				"LIMIT %d OFFSET %d;", manager, worker, like, like, limit, (page-1)*limit,
		)
	} else {
		SQL = utils.Format(
			"SELECT c.`id`,d.`id`,d.`addr`,c.`code`,c.`status`,c.`publish`,c.`finish`,"+
				"w1.`id`,w1.`name`,c.`remain1o`,w2.`id`,w2.`name`,c.`remain2o`,"+
				"w3.`id`,w3.`name`,c.`remain3o`,w4.`id`,w4.`name`,c.`remain4o`,"+
				"w5.`id`,w5.`name`,c.`remain1n`,w6.`id`,w6.`name`,c.`remain2n`,"+
				"w7.`id`,w7.`name`,c.`remain3n`,w8.`id`,w8.`name`,c.`remain4n` "+
				"FROM `change` AS c LEFT JOIN `device` AS d ON c.`device`=d.`id`"+
				"INNER JOIN `wine` AS w1 ON w1.`id`=c.`wine1o` INNER JOIN `wine` AS w2 ON w2.`id`=c.`wine2o` "+
				"INNER JOIN `wine` AS w3 ON w3.`id`=c.`wine3o` INNER JOIN `wine` AS w4 ON w4.`id`=c.`wine4o` "+
				"INNER JOIN `wine` AS w5 ON w5.`id`=c.`wine1n` INNER JOIN `wine` AS w6 ON w6.`id`=c.`wine2n` "+
				"INNER JOIN `wine` AS w7 ON w7.`id`=c.`wine3n` INNER JOIN `wine` AS w8 ON w8.`id`=c.`wine4n` "+
				"WHERE c.`status`=%d AND c.`manager`='%s' AND c.`worker`='%s' AND (d.`id` LIKE '%s' OR d.`addr` LIKE '%s') "+
				"LIMIT %d OFFSET %d;", status, manager, worker, like, like, limit, (page-1)*limit,
		)
	}
	var rows *sql.Rows
	res := make([]changeQueryRes, 0)
	rows, err = utils.Mysql.Query(SQL)
	if err != nil {
		return 0, nil, err
	}
	for rows.Next() {
		var one changeQueryRes
		_ = rows.Scan(
			&one.Id, &one.Device.Id, &one.Device.Addr,
			&one.Code, &one.Status, &one.Publish, &one.Finish,
			&one.Old[0].Id, &one.Old[0].Name, &one.Old[0].Remain,
			&one.Old[1].Id, &one.Old[1].Name, &one.Old[1].Remain,
			&one.Old[2].Id, &one.Old[2].Name, &one.Old[2].Remain,
			&one.Old[3].Id, &one.Old[3].Name, &one.Old[3].Remain,
			&one.New[0].Id, &one.New[0].Name, &one.New[0].Remain,
			&one.New[1].Id, &one.New[1].Name, &one.New[1].Remain,
			&one.New[2].Id, &one.New[2].Name, &one.New[2].Remain,
			&one.New[3].Id, &one.New[3].Name, &one.New[3].Remain,
		)
		res = append(res, one)
	}
	_ = rows.Close()
	return total, res, nil
}

type changeQueryForWorkerRes struct {
	Id     uint32 `json:"id"`
	Device struct {
		Id   string `json:"id"`
		Addr string `json:"addr"`
	} `json:"device"`
	Status  uint8          `json:"status"`
	Publish utils.TimeType `json:"publish"`
	Finish  utils.TimeType `json:"finish"`
}

func ChangesQueryForWorker(worker, cond string, status, limit, page int) (int, []changeQueryForWorkerRes, error) {
	SQL, like := "none", utils.Format("%%%s%%", cond)
	if status == -1 {
		SQL = utils.Format(
			"SELECT COUNT(c.`id`) FROM `change` AS c LEFT JOIN `device` AS d ON c.`device`=d.`id`"+
				"WHERE c.`worker`='%s' AND (d.`id` LIKE '%s' OR d.`addr` LIKE '%s')",
			worker, like, like,
		)
	} else {
		SQL = utils.Format(
			"SELECT COUNT(c.`id`) FROM `change` AS c LEFT JOIN `device` AS d ON c.`device`=d.`id`"+
				"WHERE c.`status`=%d AND c.`worker`='%s' AND (d.`id` LIKE '%s' OR d.`addr` LIKE '%s')",
			status, worker, like, like,
		)
	}
	total := 0
	err := utils.Mysql.QueryRow(SQL).Scan(&total)
	if err != nil {
		return 0, nil, err
	}
	if total == 0 {
		return 0, []changeQueryForWorkerRes{}, err
	}
	if status == -1 {
		SQL = utils.Format(
			"SELECT c.`id`,d.`id`,d.`addr`,c.`status`,c.`publish`,c.`finish` "+
				"FROM `change` AS c LEFT JOIN `device` AS d ON c.`device`=d.`id` "+
				"WHERE c.`worker`='%s' AND (d.`id` LIKE '%s' OR d.`addr` LIKE '%s') "+
				"LIMIT %d OFFSET %d;", worker, like, like, limit, (page-1)*limit,
		)
	} else {
		SQL = utils.Format(
			"SELECT c.`id`,d.`id`,d.`addr`,c.`status`,c.`publish`,c.`finish` "+
				"FROM `change` AS c LEFT JOIN `device` AS d ON c.`device`=d.`id` "+
				"WHERE c.`status`=%d AND c.`worker`='%s' AND (d.`id` LIKE '%s' OR d.`addr` LIKE '%s') "+
				"LIMIT %d OFFSET %d;", status, worker, like, like, limit, (page-1)*limit,
		)
	}
	var rows *sql.Rows
	res := make([]changeQueryForWorkerRes, 0)
	rows, err = utils.Mysql.Query(SQL)
	if err != nil {
		return 0, nil, err
	}
	for rows.Next() {
		var one changeQueryForWorkerRes
		_ = rows.Scan(
			&one.Id, &one.Device.Id, &one.Device.Addr,
			&one.Status, &one.Publish, &one.Finish,
		)
		res = append(res, one)
	}
	_ = rows.Close()
	return total, res, nil
}

func ChangeDetail(cid uint32) (*changeQueryRes, error) {
	SQL := "SELECT c.`id`,d.`id`,d.`addr`,c.`code`,c.`status`,c.`publish`,c.`finish`," +
		"w1.`id`,w1.`name`,c.`remain1o`,w2.`id`,w2.`name`,c.`remain2o`," +
		"w3.`id`,w3.`name`,c.`remain3o`,w4.`id`,w4.`name`,c.`remain4o`," +
		"w5.`id`,w5.`name`,c.`remain1n`,w6.`id`,w6.`name`,c.`remain2n`," +
		"w7.`id`,w7.`name`,c.`remain3n`,w8.`id`,w8.`name`,c.`remain4n` " +
		"FROM `change` AS c LEFT JOIN `device` AS d ON c.`device`=d.`id`" +
		"INNER JOIN `wine` AS w1 ON w1.`id`=c.`wine1o` INNER JOIN `wine` AS w2 ON w2.`id`=c.`wine2o` " +
		"INNER JOIN `wine` AS w3 ON w3.`id`=c.`wine3o` INNER JOIN `wine` AS w4 ON w4.`id`=c.`wine4o` " +
		"INNER JOIN `wine` AS w5 ON w5.`id`=c.`wine1n` INNER JOIN `wine` AS w6 ON w6.`id`=c.`wine2n` " +
		"INNER JOIN `wine` AS w7 ON w7.`id`=c.`wine3n` INNER JOIN `wine` AS w8 ON w8.`id`=c.`wine4n` " +
		"WHERE c.`id`=?;"
	var res changeQueryRes
	err := utils.Mysql.QueryRow(SQL, cid).Scan(
		&res.Id, &res.Device.Id, &res.Device.Addr,
		&res.Code, &res.Status, &res.Publish, &res.Finish,
		&res.Old[0].Id, &res.Old[0].Name, &res.Old[0].Remain,
		&res.Old[1].Id, &res.Old[1].Name, &res.Old[1].Remain,
		&res.Old[2].Id, &res.Old[2].Name, &res.Old[2].Remain,
		&res.Old[3].Id, &res.Old[3].Name, &res.Old[3].Remain,
		&res.New[0].Id, &res.New[0].Name, &res.New[0].Remain,
		&res.New[1].Id, &res.New[1].Name, &res.New[1].Remain,
		&res.New[2].Id, &res.New[2].Name, &res.New[2].Remain,
		&res.New[3].Id, &res.New[3].Name, &res.New[3].Remain,
	)
	return &res, err
}

func ChangeRankWorkers(start, end string) ([]idNameTotal, error) {
	SQL := "SELECT w.`id` AS `person`,w.`name`,COUNT(c.`id`) AS `count` FROM `change` AS c " +
		"LEFT JOIN `worker` AS w ON c.`worker`=w.`id`  WHERE c.`status`=2 " +
		"AND (c.`finish` BETWEEN TIMESTAMP(?) AND TIMESTAMP(?)) GROUP BY `person` ORDER BY `count` 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 ChangeRankWorkersForAdmin(manager, start, end string) ([]idNameTotal, error) {
	SQL := "SELECT w.`id` AS `person`,w.`name`,COUNT(c.`id`) AS `count` FROM `change` AS c " +
		"LEFT JOIN `worker` AS w ON c.`worker`=w.`id`  WHERE c.`status`=2 AND c.`manager`=? " +
		"AND (c.`finish` BETWEEN TIMESTAMP(?) AND TIMESTAMP(?)) GROUP BY `person` ORDER BY `count` 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 ChangeQueryWorker(id string, kind uint8) ([]timeTotal, error) {
	SQL := "none"
	if kind == 0 {
		SQL = "SELECT DATE_FORMAT(`finish`,'%Y-%m') as `tt`,COUNT(`id`) FROM `change` WHERE `status`=2 AND `worker`=? GROUP BY `tt`;"
	} else {
		align, days := getFmtAndDays(kind)
		SQL = utils.Format(
			"SELECT DATE_FORMAT(`finish`,'%s') as `tt`,COUNT(`tt`) FROM `change` WHERE `status`=2 AND `worker`=? AND (`finish` BETWEEN "+
				"DATE_SUB(CURRENT_TIMESTAMP,INTERVAL %d DAY) AND CURRENT_TIMESTAMP) GROUP BY `tt`;", align, days,
		)
	}
	rows, err := utils.Mysql.Query(SQL, id)
	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 ChangeQueryWorkerForAdmin(manager, id string, kind uint8) ([]timeTotal, error) {
	SQL := "none"
	if kind == 0 {
		SQL = "SELECT DATE_FORMAT(`finish`,'%Y-%m') as `tt`,COUNT(`id`) FROM `change` " +
			"WHERE `status`=2 AND `manager`=? AND `worker`=? GROUP BY `tt`;"
	} else {
		align, days := getFmtAndDays(kind)
		SQL = utils.Format(
			"SELECT DATE_FORMAT(`finish`,'%s') as `tt`,COUNT(`tt`) FROM `change` WHERE `status`=2 AND `manager`=? "+
				"AND `worker`=? AND (`finish` BETWEEN DATE_SUB(CURRENT_TIMESTAMP,INTERVAL %d DAY) AND CURRENT_TIMESTAMP) "+
				"GROUP BY `tt`;", align, days,
		)
	}
	rows, err := utils.Mysql.Query(SQL, manager, id)
	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
}