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 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 }