Instantly share code, notes, and snippets.

@yudppp /app.go
Created Sep 24, 2018

Embed
What would you like to do?
isucon2018
package main
import (
"bytes"
"crypto/sha256"
"database/sql"
"encoding/json"
"errors"
"fmt"
"html/template"
"io"
"log"
"os"
"os/exec"
"strconv"
"strings"
"sync"
"time"
_ "github.com/go-sql-driver/mysql"
"github.com/gorilla/sessions"
"github.com/labstack/echo"
"github.com/labstack/echo-contrib/session"
"github.com/labstack/echo/middleware"
)
type User struct {
ID int64 `json:"id,omitempty"`
Nickname string `json:"nickname,omitempty"`
LoginName string `json:"login_name,omitempty"`
PassHash string `json:"pass_hash,omitempty"`
}
type Event struct {
ID int64 `json:"id,omitempty"`
Title string `json:"title,omitempty"`
PublicFg bool `json:"public,omitempty"`
ClosedFg bool `json:"closed,omitempty"`
Price int64 `json:"price,omitempty"`
Total int `json:"total"`
Remains int `json:"remains"`
Sheets map[string]*Sheets `json:"sheets,omitempty"`
}
type Sheets struct {
Total int `json:"total"`
Remains int `json:"remains"`
Detail []*Sheet `json:"detail,omitempty"`
Price int64 `json:"price"`
}
type Sheet struct {
ID int64 `json:"-"`
Rank string `json:"-"`
Num int64 `json:"num"`
Price int64 `json:"-"`
Mine bool `json:"mine,omitempty"`
Reserved bool `json:"reserved,omitempty"`
ReservedAt *time.Time `json:"-"`
ReservedAtUnix int64 `json:"reserved_at,omitempty"`
}
type Reservation struct {
ID int64 `json:"id"`
EventID int64 `json:"-"`
SheetID int64 `json:"-"`
UserID int64 `json:"-"`
ReservedAt *time.Time `json:"-"`
CanceledAt *time.Time `json:"-"`
Event *Event `json:"event,omitempty"`
SheetRank string `json:"sheet_rank,omitempty"`
SheetNum int64 `json:"sheet_num,omitempty"`
Price int64 `json:"price,omitempty"`
ReservedAtUnix int64 `json:"reserved_at,omitempty"`
CanceledAtUnix int64 `json:"canceled_at,omitempty"`
}
type Administrator struct {
ID int64 `json:"id,omitempty"`
Nickname string `json:"nickname,omitempty"`
LoginName string `json:"login_name,omitempty"`
PassHash string `json:"pass_hash,omitempty"`
}
func sessUserID(c echo.Context) int64 {
sess, _ := session.Get("session", c)
var userID int64
if x, ok := sess.Values["user_id"]; ok {
userID, _ = x.(int64)
}
return userID
}
func sessSetUserID(c echo.Context, id int64) {
sess, _ := session.Get("session", c)
sess.Options = &sessions.Options{
Path: "/",
MaxAge: 3600,
HttpOnly: true,
}
sess.Values["user_id"] = id
sess.Save(c.Request(), c.Response())
}
func sessDeleteUserID(c echo.Context) {
sess, _ := session.Get("session", c)
sess.Options = &sessions.Options{
Path: "/",
MaxAge: 3600,
HttpOnly: true,
}
delete(sess.Values, "user_id")
sess.Save(c.Request(), c.Response())
}
func sessAdministratorID(c echo.Context) int64 {
sess, _ := session.Get("session", c)
var administratorID int64
if x, ok := sess.Values["administrator_id"]; ok {
administratorID, _ = x.(int64)
}
return administratorID
}
func sessSetAdministratorID(c echo.Context, id int64) {
sess, _ := session.Get("session", c)
sess.Options = &sessions.Options{
Path: "/",
MaxAge: 3600,
HttpOnly: true,
}
sess.Values["administrator_id"] = id
sess.Save(c.Request(), c.Response())
}
func sessDeleteAdministratorID(c echo.Context) {
sess, _ := session.Get("session", c)
sess.Options = &sessions.Options{
Path: "/",
MaxAge: 3600,
HttpOnly: true,
}
delete(sess.Values, "administrator_id")
sess.Save(c.Request(), c.Response())
}
func loginRequired(next echo.HandlerFunc) echo.HandlerFunc {
return func(c echo.Context) error {
if _, err := getLoginUser(c); err != nil {
return resError(c, "login_required", 401)
}
return next(c)
}
}
func adminLoginRequired(next echo.HandlerFunc) echo.HandlerFunc {
return func(c echo.Context) error {
if _, err := getLoginAdministrator(c); err != nil {
return resError(c, "admin_login_required", 401)
}
return next(c)
}
}
func getLoginUser(c echo.Context) (*User, error) {
userID := sessUserID(c)
if userID == 0 {
return nil, errors.New("not logged in")
}
var user User
err := db.QueryRow("SELECT id, nickname FROM users WHERE id = ?", userID).Scan(&user.ID, &user.Nickname)
return &user, err
}
func getLoginAdministrator(c echo.Context) (*Administrator, error) {
administratorID := sessAdministratorID(c)
if administratorID == 0 {
return nil, errors.New("not logged in")
}
var administrator Administrator
err := db.QueryRow("SELECT id, nickname FROM administrators WHERE id = ?", administratorID).Scan(&administrator.ID, &administrator.Nickname)
return &administrator, err
}
func getEventsSimpleAll() ([]*Event, error) {
tx, err := db.Begin()
if err != nil {
return nil, err
}
defer tx.Commit()
rows, err := tx.Query("SELECT * FROM events ORDER BY id ASC")
if err != nil {
return nil, err
}
defer rows.Close()
var events []*Event
for rows.Next() {
var event Event
if err := rows.Scan(&event.ID, &event.Title, &event.PublicFg, &event.ClosedFg, &event.Price); err != nil {
return nil, err
}
events = append(events, &event)
}
return events, nil
}
func getEvents(all bool) ([]*Event, error) {
tx, err := db.Begin()
if err != nil {
return nil, err
}
defer tx.Commit()
rows, err := tx.Query("SELECT * FROM events ORDER BY id ASC")
if err != nil {
return nil, err
}
defer rows.Close()
var events []*Event
eventIDs := make([]int64, 0, 32)
reservationsMap := make(map[int64]map[int64]Reservation, 32)
for rows.Next() {
var event Event
if err := rows.Scan(&event.ID, &event.Title, &event.PublicFg, &event.ClosedFg, &event.Price); err != nil {
return nil, err
}
if !all && !event.PublicFg {
continue
}
event.Sheets = map[string]*Sheets{
"S": &Sheets{},
"A": &Sheets{},
"B": &Sheets{},
"C": &Sheets{},
}
events = append(events, &event)
eventIDs = append(eventIDs, event.ID)
reservationsMap[event.ID] = make(map[int64]Reservation, 1000)
}
query := fmt.Sprintf("SELECT * FROM reservations WHERE event_id in (%s) AND canceled_at IS NULL GROUP BY event_id, sheet_id HAVING reserved_at = MIN(reserved_at)",
strings.TrimRight(strings.Repeat("?,", len(eventIDs)), ","))
args := make([]interface{}, len(eventIDs))
for i, v := range eventIDs {
args[i] = v
}
rows2, err := db.Query(query, args...)
if err != nil {
log.Println(err)
return nil, err
}
for rows2.Next() {
var reservation Reservation
if err := rows2.Scan(&reservation.ID, &reservation.EventID, &reservation.SheetID, &reservation.UserID, &reservation.ReservedAt, &reservation.CanceledAt); err != nil {
return nil, err
}
reservationsMap[reservation.EventID][reservation.SheetID] = reservation
}
for i, v := range events {
if v == nil {
continue
}
event, err := getEventSimpleWithEventAndReservationsMap(*v, reservationsMap[v.ID])
if err != nil {
return nil, err
}
for k := range event.Sheets {
event.Sheets[k].Detail = nil
}
events[i] = event
}
return events, nil
}
func getEventSimple(eventID int64) (*Event, error) {
var event Event
if err := db.QueryRow("SELECT * FROM events WHERE id = ?", eventID).Scan(&event.ID, &event.Title, &event.PublicFg, &event.ClosedFg, &event.Price); err != nil {
return nil, err
}
return &event, nil
}
func getEventSimpleWithEventAndReservationsMap(event Event, reservationsMap map[int64]Reservation) (*Event, error) {
for _, order := range masterSheetsRankOrder {
sheets, ok := masterSheets[order]
if !ok {
continue
}
for _, s := range sheets {
sheet := Sheet{
ID: s.ID,
Rank: s.Rank,
Num: s.Num,
Price: s.Price,
}
event.Sheets[sheet.Rank].Price = event.Price + sheet.Price
event.Total++
event.Sheets[sheet.Rank].Total++
reservation, ok := reservationsMap[sheet.ID]
if ok {
sheet.Mine = false
sheet.Reserved = true
sheet.ReservedAtUnix = reservation.ReservedAt.Unix()
} else {
event.Remains++
event.Sheets[sheet.Rank].Remains++
}
}
}
return &event, nil
}
func getEventWithEventAndReservationsMap(event Event, reservationsMap map[int64]Reservation, loginUserID int64) (*Event, error) {
for _, order := range masterSheetsRankOrder {
sheets, ok := masterSheets[order]
if !ok {
continue
}
for _, s := range sheets {
sheet := Sheet{
ID: s.ID,
Rank: s.Rank,
Num: s.Num,
Price: s.Price,
}
event.Sheets[sheet.Rank].Price = event.Price + sheet.Price
event.Total++
event.Sheets[sheet.Rank].Total++
reservation, ok := reservationsMap[sheet.ID]
if ok {
sheet.Mine = reservation.UserID == loginUserID
sheet.Reserved = true
sheet.ReservedAtUnix = reservation.ReservedAt.Unix()
} else {
event.Remains++
event.Sheets[sheet.Rank].Remains++
}
event.Sheets[sheet.Rank].Detail = append(event.Sheets[sheet.Rank].Detail, &sheet)
}
}
return &event, nil
}
func getEventWithEvent(event Event, loginUserID int64) (*Event, error) {
event.Sheets = map[string]*Sheets{
"S": &Sheets{},
"A": &Sheets{},
"B": &Sheets{},
"C": &Sheets{},
}
reservationsMap := make(map[int64]Reservation, 1000)
rows, _ := db.Query("SELECT * FROM reservations WHERE event_id = ? AND canceled_at IS NULL GROUP BY event_id, sheet_id HAVING reserved_at = MIN(reserved_at)", event.ID)
for rows.Next() {
var reservation Reservation
if err := rows.Scan(&reservation.ID, &reservation.EventID, &reservation.SheetID, &reservation.UserID, &reservation.ReservedAt, &reservation.CanceledAt); err != nil {
return nil, err
}
reservationsMap[reservation.SheetID] = reservation
}
return getEventWithEventAndReservationsMap(event, reservationsMap, loginUserID)
}
func getEvent(eventID, loginUserID int64) (*Event, error) {
var event Event
if err := db.QueryRow("SELECT * FROM events WHERE id = ?", eventID).Scan(&event.ID, &event.Title, &event.PublicFg, &event.ClosedFg, &event.Price); err != nil {
return nil, err
}
return getEventWithEvent(event, loginUserID)
}
func getSheet(rank string, num string) Sheet {
if sheets, ok := masterSheets[rank]; ok {
for _, sheet := range sheets {
if fmt.Sprint(sheet.Num) == num {
return sheet
}
}
}
return Sheet{}
}
func getSheetByID(id int64) (Sheet, bool) {
sheet, ok := masterSheetsMap[id]
return sheet, ok
}
func sanitizeEvent(e *Event) *Event {
sanitized := *e
sanitized.Price = 0
sanitized.PublicFg = false
sanitized.ClosedFg = false
return &sanitized
}
func fillinUser(next echo.HandlerFunc) echo.HandlerFunc {
return func(c echo.Context) error {
if user, err := getLoginUser(c); err == nil {
c.Set("user", user)
}
return next(c)
}
}
func fillinAdministrator(next echo.HandlerFunc) echo.HandlerFunc {
return func(c echo.Context) error {
if administrator, err := getLoginAdministrator(c); err == nil {
c.Set("administrator", administrator)
}
return next(c)
}
}
func validateRank(rank string) bool {
_, ok := masterSheets[rank]
return ok
}
type Renderer struct {
templates *template.Template
}
func (r *Renderer) Render(w io.Writer, name string, data interface{}, c echo.Context) error {
return r.templates.ExecuteTemplate(w, name, data)
}
var db *sql.DB
var masterSheets = make(map[string][]Sheet, 4)
var masterSheetsMap = make(map[int64]Sheet, 1000)
var masterSheetsRankOrder = make([]string, 0, 4)
func initialize() {
// initalize
{
rows, err := db.Query("SELECT * FROM sheets ORDER BY `rank`, num")
if err != nil {
panic(err)
}
defer rows.Close()
masterSheets = make(map[string][]Sheet, 4)
masterSheetsRankOrder = make([]string, 0, 4)
for rows.Next() {
var sheet Sheet
if err := rows.Scan(&sheet.ID, &sheet.Rank, &sheet.Num, &sheet.Price); err != nil {
panic(err)
}
if sheets, ok := masterSheets[sheet.Rank]; ok {
masterSheets[sheet.Rank] = append(sheets, Sheet{
ID: sheet.ID,
Rank: sheet.Rank,
Num: sheet.Num,
Price: sheet.Price,
})
} else {
sheets := make([]Sheet, 0, 500)
sheets = append(sheets, Sheet{
ID: sheet.ID,
Rank: sheet.Rank,
Num: sheet.Num,
Price: sheet.Price,
})
masterSheets[sheet.Rank] = sheets
masterSheetsRankOrder = append(masterSheetsRankOrder, sheet.Rank)
}
masterSheetsMap[sheet.ID] = sheet
}
}
}
func initialize2() {
syncEventSheetMap = sync.Map{}
rows, _ := db.Query("SELECT * FROM reservations WHERE canceled_at IS NULL GROUP BY event_id, sheet_id HAVING reserved_at = MIN(reserved_at)")
for rows.Next() {
var reservation Reservation
if err := rows.Scan(&reservation.ID, &reservation.EventID, &reservation.SheetID, &reservation.UserID, &reservation.ReservedAt, &reservation.CanceledAt); err != nil {
panic(err)
}
key := fmt.Sprintf("%v::%v", reservation.EventID, reservation.SheetID)
syncEventSheetMap.Store(key, reservation.UserID)
}
}
var syncEventSheetMap = sync.Map{}
var syncEventMap map[int]sync.Map
func main() {
dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?parseTime=true&charset=utf8mb4",
os.Getenv("DB_USER"), os.Getenv("DB_PASS"),
os.Getenv("DB_HOST"), os.Getenv("DB_PORT"),
os.Getenv("DB_DATABASE"),
)
var err error
db, err = sql.Open("mysql", dsn)
if err != nil {
log.Fatal(err)
}
initialize()
e := echo.New()
funcs := template.FuncMap{
"encode_json": func(v interface{}) string {
b, _ := json.Marshal(v)
return string(b)
},
}
e.Renderer = &Renderer{
templates: template.Must(template.New("").Delims("[[", "]]").Funcs(funcs).ParseGlob("views/*.tmpl")),
}
e.Use(session.Middleware(sessions.NewCookieStore([]byte("secret"))))
e.Use(middleware.LoggerWithConfig(middleware.LoggerConfig{Output: os.Stderr}))
e.Static("/", "public")
e.GET("/", func(c echo.Context) error {
events, err := getEvents(false)
if err != nil {
return err
}
for i, v := range events {
events[i] = sanitizeEvent(v)
}
return c.Render(200, "index.tmpl", echo.Map{
"events": events,
"user": c.Get("user"),
"origin": c.Scheme() + "://" + c.Request().Host,
})
}, fillinUser)
e.GET("/initialize", func(c echo.Context) error {
cmd := exec.Command("../../db/init.sh")
cmd.Stdin = os.Stdin
cmd.Stdout = os.Stdout
err := cmd.Run()
if err != nil {
return nil
}
// initialize()
initialize2()
return c.NoContent(204)
})
e.POST("/api/users", func(c echo.Context) error {
var params struct {
Nickname string `json:"nickname"`
LoginName string `json:"login_name"`
Password string `json:"password"`
}
c.Bind(&params)
tx, err := db.Begin()
if err != nil {
return err
}
var user User
if err := tx.QueryRow("SELECT * FROM users WHERE login_name = ?", params.LoginName).Scan(&user.ID, &user.LoginName, &user.Nickname, &user.PassHash); err != sql.ErrNoRows {
tx.Rollback()
if err == nil {
return resError(c, "duplicated", 409)
}
return err
}
res, err := tx.Exec("INSERT INTO users (login_name, pass_hash, nickname) VALUES (?, SHA2(?, 256), ?)", params.LoginName, params.Password, params.Nickname)
if err != nil {
tx.Rollback()
return resError(c, "", 0)
}
userID, err := res.LastInsertId()
if err != nil {
tx.Rollback()
return resError(c, "", 0)
}
if err := tx.Commit(); err != nil {
return err
}
return c.JSON(201, echo.Map{
"id": userID,
"nickname": params.Nickname,
})
})
e.GET("/api/users/:id", func(c echo.Context) error {
loginUser, err := getLoginUser(c)
if err != nil {
return err
}
userID := c.Param("id")
if userID != fmt.Sprint(loginUser.ID) {
return resError(c, "forbidden", 403)
}
var user User
if err := db.QueryRow("SELECT id, nickname FROM users WHERE id = ?", userID).Scan(&user.ID, &user.Nickname); err != nil {
return err
}
rows, err := db.Query("SELECT * FROM reservations WHERE user_id = ? ORDER BY IFNULL(canceled_at, reserved_at) DESC LIMIT 5", user.ID)
if err != nil {
return err
}
defer rows.Close()
var recentReservations []Reservation
for rows.Next() {
var reservation Reservation
if err := rows.Scan(&reservation.ID, &reservation.EventID, &reservation.SheetID, &reservation.UserID, &reservation.ReservedAt, &reservation.CanceledAt); err != nil {
return err
}
sheet, _ := getSheetByID(reservation.SheetID)
event, err := getEventSimple(reservation.EventID)
if err != nil {
return err
}
reservation.Event = event
reservation.SheetRank = sheet.Rank
reservation.SheetNum = sheet.Num
reservation.Price = sheet.Price + event.Price
reservation.ReservedAtUnix = reservation.ReservedAt.Unix()
if reservation.CanceledAt != nil {
reservation.CanceledAtUnix = reservation.CanceledAt.Unix()
}
recentReservations = append(recentReservations, reservation)
}
if recentReservations == nil {
recentReservations = make([]Reservation, 0)
}
var totalPrice int
if err := db.QueryRow("SELECT IFNULL(SUM(e.price + s.price), 0) FROM reservations r INNER JOIN sheets s ON s.id = r.sheet_id INNER JOIN events e ON e.id = r.event_id WHERE r.user_id = ? AND r.canceled_at IS NULL", user.ID).Scan(&totalPrice); err != nil {
return err
}
rows, err = db.Query("SELECT event_id FROM reservations WHERE user_id = ? GROUP BY event_id ORDER BY MAX(IFNULL(canceled_at, reserved_at)) DESC LIMIT 5", user.ID)
if err != nil {
return err
}
defer rows.Close()
var recentEvents []*Event
for rows.Next() {
var eventID int64
if err := rows.Scan(&eventID); err != nil {
return err
}
event, err := getEvent(eventID, -1)
if err != nil {
return err
}
for k := range event.Sheets {
event.Sheets[k].Detail = nil
}
recentEvents = append(recentEvents, event)
}
if recentEvents == nil {
recentEvents = make([]*Event, 0)
}
return c.JSON(200, echo.Map{
"id": user.ID,
"nickname": user.Nickname,
"recent_reservations": recentReservations,
"total_price": totalPrice,
"recent_events": recentEvents,
})
}, loginRequired)
e.POST("/api/actions/login", func(c echo.Context) error {
var params struct {
LoginName string `json:"login_name"`
Password string `json:"password"`
}
c.Bind(&params)
user := new(User)
if err := db.QueryRow("SELECT * FROM users WHERE login_name = ?", params.LoginName).Scan(&user.ID, &user.LoginName, &user.Nickname, &user.PassHash); err != nil {
if err == sql.ErrNoRows {
return resError(c, "authentication_failed", 401)
}
return err
}
passHash := fmt.Sprintf("%x", sha256.Sum256([]byte(params.Password)))
if user.PassHash != passHash {
return resError(c, "authentication_failed", 401)
}
sessSetUserID(c, user.ID)
user, err = getLoginUser(c)
if err != nil {
return err
}
return c.JSON(200, user)
})
e.POST("/api/actions/logout", func(c echo.Context) error {
sessDeleteUserID(c)
return c.NoContent(204)
}, loginRequired)
e.GET("/api/events", func(c echo.Context) error {
events, err := getEvents(true)
if err != nil {
return err
}
for i, v := range events {
events[i] = sanitizeEvent(v)
}
return c.JSON(200, events)
})
e.GET("/api/events/:id", func(c echo.Context) error {
eventID, err := strconv.ParseInt(c.Param("id"), 10, 64)
if err != nil {
return resError(c, "not_found", 404)
}
loginUserID := int64(-1)
if user, err := getLoginUser(c); err == nil {
loginUserID = user.ID
}
event, err := getEvent(eventID, loginUserID)
if err != nil {
if err == sql.ErrNoRows {
return resError(c, "not_found", 404)
}
return err
} else if !event.PublicFg {
return resError(c, "not_found", 404)
}
return c.JSON(200, sanitizeEvent(event))
})
var count int
e.POST("/api/events/:id/actions/reserve", func(c echo.Context) error {
eventID, err := strconv.ParseInt(c.Param("id"), 10, 64)
if err != nil {
return resError(c, "not_found", 404)
}
var params struct {
Rank string `json:"sheet_rank"`
}
c.Bind(&params)
user, err := getLoginUser(c)
if err != nil {
return err
}
event, err := getEventSimple(eventID)
if err != nil {
if err == sql.ErrNoRows {
return resError(c, "invalid_event", 404)
}
return err
} else if !event.PublicFg {
return resError(c, "invalid_event", 404)
}
if !validateRank(params.Rank) {
return resError(c, "invalid_rank", 400)
}
sheets := make(map[int64]Sheet, 1000)
var query string
if count++; count%2 == 0 {
query = "SELECT * FROM sheets WHERE id NOT IN (SELECT sheet_id FROM reservations WHERE event_id = ? AND canceled_at IS NULL) AND `rank` = ? ORDER BY id desc"
} else {
query = "SELECT * FROM sheets WHERE id NOT IN (SELECT sheet_id FROM reservations WHERE event_id = ? AND canceled_at IS NULL) AND `rank` = ? ORDER BY id asc"
}
rows, err := db.Query(query, event.ID, params.Rank)
if err != nil {
if err == sql.ErrNoRows {
return resError(c, "sold_out", 409)
}
return err
}
for rows.Next() {
var sheet Sheet
rows.Scan(&sheet.ID, &sheet.Rank, &sheet.Num, &sheet.Price)
sheets[sheet.ID] = sheet
}
// 予約が取れるまで挑戦する
for _, sheet := range sheets {
tx, err := db.Begin()
if err != nil {
return err
}
key := fmt.Sprintf("%v::%v", event.ID, sheet.ID)
_, loaded := syncEventSheetMap.LoadOrStore(key, user.ID)
if loaded {
continue
}
res, err := tx.Exec("INSERT INTO reservations (event_id, sheet_id, user_id, reserved_at) VALUES (?, ?, ?, ?)", event.ID, sheet.ID, user.ID, time.Now().UTC().Format("2006-01-02 15:04:05.000000"))
if err != nil {
tx.Rollback()
log.Println("re-try: rollback by", err)
continue
}
reservationID, err := res.LastInsertId()
if err != nil {
tx.Rollback()
log.Println("re-try: rollback by", err)
continue
}
if err := tx.Commit(); err != nil {
tx.Rollback()
log.Println("re-try: rollback by", err)
continue
}
return c.JSON(202, echo.Map{
"id": reservationID,
"sheet_rank": params.Rank,
"sheet_num": sheet.Num,
})
}
return resError(c, "sold_out", 409)
}, loginRequired)
e.DELETE("/api/events/:id/sheets/:rank/:num/reservation", func(c echo.Context) error {
eventID, err := strconv.ParseInt(c.Param("id"), 10, 64)
if err != nil {
return resError(c, "not_found", 404)
}
rank := c.Param("rank")
num := c.Param("num")
user, err := getLoginUser(c)
if err != nil {
return err
}
event, err := getEventSimple(eventID)
if err != nil {
if err == sql.ErrNoRows {
return resError(c, "invalid_event", 404)
}
return err
} else if !event.PublicFg {
return resError(c, "invalid_event", 404)
}
if !validateRank(rank) {
return resError(c, "invalid_rank", 404)
}
sheet := getSheet(rank, num)
if sheet.ID == 0 {
return resError(c, "invalid_sheet", 404)
}
key := fmt.Sprintf("%v::%v", event.ID, sheet.ID)
// sheetUserID, ok := syncEventSheetMap.Load(key)
// if !ok {
// return resError(c, "not_reserved", 400)
// }
// if sheetUserID != user.ID {
// return resError(c, "not_permitted", 403)
// }
var reservation Reservation
if err := db.QueryRow("SELECT * FROM reservations WHERE event_id = ? AND sheet_id = ? AND canceled_at IS NULL GROUP BY event_id HAVING reserved_at = MIN(reserved_at)", event.ID, sheet.ID).Scan(&reservation.ID, &reservation.EventID, &reservation.SheetID, &reservation.UserID, &reservation.ReservedAt, &reservation.CanceledAt); err != nil {
if err == sql.ErrNoRows {
return resError(c, "not_reserved", 400)
}
return err
}
if reservation.UserID != user.ID {
return resError(c, "not_permitted", 403)
}
if _, err := db.Exec("UPDATE reservations SET canceled_at = ? WHERE id = ?", time.Now().UTC().Format("2006-01-02 15:04:05.000000"), reservation.ID); err != nil {
return err
}
syncEventSheetMap.Delete(key)
return c.NoContent(204)
}, loginRequired)
e.GET("/admin/", func(c echo.Context) error {
var events []*Event
administrator := c.Get("administrator")
if administrator != nil {
var err error
if events, err = getEvents(true); err != nil {
return err
}
}
return c.Render(200, "admin.tmpl", echo.Map{
"events": events,
"administrator": administrator,
"origin": c.Scheme() + "://" + c.Request().Host,
})
}, fillinAdministrator)
e.POST("/admin/api/actions/login", func(c echo.Context) error {
var params struct {
LoginName string `json:"login_name"`
Password string `json:"password"`
}
c.Bind(&params)
administrator := new(Administrator)
if err := db.QueryRow("SELECT * FROM administrators WHERE login_name = ?", params.LoginName).Scan(&administrator.ID, &administrator.LoginName, &administrator.Nickname, &administrator.PassHash); err != nil {
if err == sql.ErrNoRows {
return resError(c, "authentication_failed", 401)
}
return err
}
passHash := fmt.Sprintf("%x", sha256.Sum256([]byte(params.Password)))
if administrator.PassHash != passHash {
return resError(c, "authentication_failed", 401)
}
sessSetAdministratorID(c, administrator.ID)
administrator, err = getLoginAdministrator(c)
if err != nil {
return err
}
return c.JSON(200, administrator)
})
e.POST("/admin/api/actions/logout", func(c echo.Context) error {
sessDeleteAdministratorID(c)
return c.NoContent(204)
}, adminLoginRequired)
e.GET("/admin/api/events", func(c echo.Context) error {
events, err := getEvents(true)
if err != nil {
return err
}
return c.JSON(200, events)
}, adminLoginRequired)
e.POST("/admin/api/events", func(c echo.Context) error {
var params struct {
Title string `json:"title"`
Public bool `json:"public"`
Price int `json:"price"`
}
c.Bind(&params)
tx, err := db.Begin()
if err != nil {
return err
}
res, err := tx.Exec("INSERT INTO events (title, public_fg, closed_fg, price) VALUES (?, ?, 0, ?)", params.Title, params.Public, params.Price)
if err != nil {
tx.Rollback()
return err
}
eventID, err := res.LastInsertId()
if err != nil {
tx.Rollback()
return err
}
if err := tx.Commit(); err != nil {
return err
}
event, err := getEvent(eventID, -1)
if err != nil {
return err
}
return c.JSON(200, event)
}, adminLoginRequired)
e.GET("/admin/api/events/:id", func(c echo.Context) error {
eventID, err := strconv.ParseInt(c.Param("id"), 10, 64)
if err != nil {
return resError(c, "not_found", 404)
}
event, err := getEvent(eventID, -1)
if err != nil {
if err == sql.ErrNoRows {
return resError(c, "not_found", 404)
}
return err
}
return c.JSON(200, event)
}, adminLoginRequired)
e.POST("/admin/api/events/:id/actions/edit", func(c echo.Context) error {
eventID, err := strconv.ParseInt(c.Param("id"), 10, 64)
if err != nil {
return resError(c, "not_found", 404)
}
var params struct {
Public bool `json:"public"`
Closed bool `json:"closed"`
}
c.Bind(&params)
if params.Closed {
params.Public = false
}
event, err := getEventSimple(eventID)
if err != nil {
if err == sql.ErrNoRows {
return resError(c, "not_found", 404)
}
return err
}
if event.ClosedFg {
return resError(c, "cannot_edit_closed_event", 400)
} else if event.PublicFg && params.Closed {
return resError(c, "cannot_close_public_event", 400)
}
tx, err := db.Begin()
if err != nil {
return err
}
if _, err := tx.Exec("UPDATE events SET public_fg = ?, closed_fg = ? WHERE id = ?", params.Public, params.Closed, event.ID); err != nil {
tx.Rollback()
return err
}
if err := tx.Commit(); err != nil {
return err
}
e, err := getEvent(eventID, -1)
if err != nil {
return err
}
c.JSON(200, e)
return nil
}, adminLoginRequired)
e.GET("/admin/api/reports/events/:id/sales", func(c echo.Context) error {
eventID, err := strconv.ParseInt(c.Param("id"), 10, 64)
if err != nil {
return resError(c, "not_found", 404)
}
event, err := getEventSimple(eventID)
if err != nil {
return err
}
rows, err := db.Query("SELECT * FROM reservations WHERE event_id = ? ORDER BY id ASC", event.ID)
if err != nil {
return err
}
defer rows.Close()
var reports []Report
for rows.Next() {
var reservation Reservation
if err := rows.Scan(&reservation.ID, &reservation.EventID, &reservation.SheetID, &reservation.UserID, &reservation.ReservedAt, &reservation.CanceledAt); err != nil {
return err
}
sheet, ok := getSheetByID(reservation.SheetID)
if !ok {
continue
}
report := Report{
ReservationID: reservation.ID,
EventID: event.ID,
Rank: sheet.Rank,
Num: sheet.Num,
UserID: reservation.UserID,
SoldAt: reservation.ReservedAt.Format("2006-01-02T15:04:05.000000Z"),
Price: event.Price + sheet.Price,
}
if reservation.CanceledAt != nil {
report.CanceledAt = reservation.CanceledAt.Format("2006-01-02T15:04:05.000000Z")
}
reports = append(reports, report)
}
return renderReportCSV(c, reports)
}, adminLoginRequired)
e.GET("/admin/api/reports/sales", func(c echo.Context) error {
time.Sleep(time.Second * 15)
rows, err := db.Query("select * from reservations order by id asc")
if err != nil {
return err
}
defer rows.Close()
events, err := getEventsSimpleAll()
if err != nil {
return err
}
eventMap := make(map[int64]*Event, len(events))
for _, v := range events {
eventMap[v.ID] = v
}
var reports []Report
for rows.Next() {
var reservation Reservation
if err := rows.Scan(&reservation.ID, &reservation.EventID, &reservation.SheetID, &reservation.UserID, &reservation.ReservedAt, &reservation.CanceledAt); err != nil {
return err
}
event, ok := eventMap[reservation.EventID]
if !ok {
continue
}
sheet, ok := getSheetByID(reservation.SheetID)
if !ok {
continue
}
report := Report{
ReservationID: reservation.ID,
EventID: event.ID,
Rank: sheet.Rank,
Num: sheet.Num,
UserID: reservation.UserID,
SoldAt: reservation.ReservedAt.Format("2006-01-02T15:04:05.000000Z"),
Price: event.Price + sheet.Price,
}
if reservation.CanceledAt != nil {
report.CanceledAt = reservation.CanceledAt.Format("2006-01-02T15:04:05.000000Z")
}
reports = append(reports, report)
}
return renderReportCSV(c, reports)
}, adminLoginRequired)
e.Start(":8080")
}
type Report struct {
ReservationID int64
EventID int64
Rank string
Num int64
UserID int64
SoldAt string
CanceledAt string
Price int64
}
func renderReportCSV(c echo.Context, reports []Report) error {
// sort.Slice(reports, func(i, j int) bool { return strings.Compare(reports[i].SoldAt, reports[j].SoldAt) < 0 })
body := bytes.NewBufferString("reservation_id,event_id,rank,num,price,user_id,sold_at,canceled_at\n")
for _, v := range reports {
body.WriteString(fmt.Sprintf("%d,%d,%s,%d,%d,%d,%s,%s\n",
v.ReservationID, v.EventID, v.Rank, v.Num, v.Price, v.UserID, v.SoldAt, v.CanceledAt))
}
c.Response().Header().Set("Content-Type", `text/csv; charset=UTF-8`)
c.Response().Header().Set("Content-Disposition", `attachment; filename="report.csv"`)
_, err := io.Copy(c.Response(), body)
return err
}
func resError(c echo.Context, e string, status int) error {
if e == "" {
e = "unknown"
}
if status < 100 {
status = 500
}
return c.JSON(status, map[string]string{"error": e})
}
CREATE TABLE IF NOT EXISTS users (
id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
nickname VARCHAR(128) NOT NULL,
login_name VARCHAR(128) NOT NULL,
pass_hash VARCHAR(128) NOT NULL,
UNIQUE KEY login_name_uniq (login_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS events (
id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(128) NOT NULL,
public_fg TINYINT(1) NOT NULL,
closed_fg TINYINT(1) NOT NULL,
price INTEGER UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS sheets (
id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
`rank` VARCHAR(128) NOT NULL,
num INTEGER UNSIGNED NOT NULL,
price INTEGER UNSIGNED NOT NULL,
UNIQUE KEY rank_num_uniq (`rank`, num)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS reservations (
id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
event_id INTEGER UNSIGNED NOT NULL,
sheet_id INTEGER UNSIGNED NOT NULL,
user_id INTEGER UNSIGNED NOT NULL,
reserved_at DATETIME(6) NOT NULL,
canceled_at DATETIME(6) DEFAULT NULL,
KEY event_id_and_sheet_id_idx (event_id, sheet_id),,
KEY user_id_idx (user_id),
KEY event_id_and_canceled_at_idx (event_id, canceled_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS administrators (
id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
nickname VARCHAR(128) NOT NULL,
login_name VARCHAR(128) NOT NULL,
pass_hash VARCHAR(128) NOT NULL,
UNIQUE KEY login_name_uniq (login_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment