func GetUserStats(user_id int) [5]int {
var result [5]int
for i := 0; i < 5; i++ {
result[i] = 0
}
sql := `
SELECT count(*) as total, level FROM (
SELECT uw.word_id, uw.level
FROM users_types ut
INNER JOIN words_types wt ON wt.type_id = ut.type_id
INNER JOIN users_words uw ON uw.word_id = wt.word_id AND uw.user_id = ut.user_id
WHERE ut.enabled = 1 AND ut.user_id = ` + strconv.Itoa(user_id) + `
GROUP BY uw.word_id
) as sub
GROUP BY level
`
stats, _ := data.GetRows(sql)
for _, stat := range stats {
index, _ := strconv.Atoi(stat["level"])
total, _ := strconv.Atoi(stat["total"])
result[index] = total
}
return result
}
func GetUsersVocabulary(user_id int, from string, kanji_study bool) ([]map[string]string, error) {
// Query is initialized
var sql string
if kanji_study {
sql = `SELECT
w.word_id,
IF(w.kanji != '', w.kanji, w.jp) as hint,
IF(w.kanji != '', w.jp, w.` + from + `) as answer,
IF(w.kanji != '', w.` + from + `, '') as secondary_answer, uw.level`
} else {
sql = `SELECT w.word_id, w.` + from + ` as hint, w.kanji as answer, w.jp as secondary_answer, uw.level`
}
// Main query body
sql += `
FROM words w
INNER JOIN users_words uw ON uw.word_id = w.word_id
INNER JOIN words_types wt ON wt.word_id = w.word_id
INNER JOIN users_types ut ON ut.type_id = wt.type_id AND ut.user_id = uw.user_id
WHERE ut.enabled = 1 AND uw.user_id = ` + strconv.Itoa(user_id) + `
GROUP BY w.word_id`
// Results are ordered by word_id
sql += `
ORDER BY w.word_id ASC
`
return data.GetRows(sql)
}
func GetUsersConfig(user_id int, from string, to string) ([]map[string]string, error) {
sql := `
SELECT t.type, t.type_id, IF(ut.type_id IS NULL, 0, ut.enabled) as enabled
FROM types t
LEFT JOIN users_types ut ON ut.type_id = t.type_id AND ut.user_id = ` + strconv.Itoa(user_id) + `
ORDER BY type`
return data.GetRows(sql)
}
func UserExists(username string) bool {
result, _ := data.GetRows("SELECT user_id FROM users WHERE username = '******'")
if result == nil {
return false
} else {
return true
}
}
// Gets vocabulary for a given type
func GetVocabByType(type_id string, from string) ([]map[string]string, error) {
sql := `
SELECT w.` + from + ` as word, w.kanji, w.jp
FROM words w
INNER JOIN words_types wt ON wt.word_id = w.word_id
WHERE wt.type_id = ` + type_id + `
ORDER BY word ASC
`
return data.GetRows(sql)
}
func CheckUserCredentials(username string, password string) (int, error) {
result, _ := data.GetRows("SELECT user_id FROM users WHERE username = '******' AND password = '******'")
if result == nil {
return 0, errors.New("user-not-found")
} else {
user_id, _ := strconv.Atoi(result[0]["user_id"])
return user_id, nil
}
}
// Gets word_ids for which the user user_id doesn't have an entry in users_words
func GetMissingUserWordsForUser(word_ids string, user_id int) []string {
var result []string
sql := `
SELECT w.word_id
FROM words w
LEFT JOIN users_words uw ON uw.word_id = w.word_id AND uw.user_id = ` + strconv.Itoa(user_id) + `
WHERE w.word_id IN (` + word_ids + `) AND uw.user_id IS NULL
`
words, _ := data.GetRows(sql)
for _, word := range words {
result = append(result, word["word_id"])
}
return result
}
// Gets all word_ids of words with a given type
func GetWordIdsByType(type_id string) []string {
var result []string
sql := `
SELECT w.word_id
FROM words w
INNER JOIN words_types wt ON wt.word_id = w.word_id
WHERE wt.type_id = ` + type_id + `
`
words, _ := data.GetRows(sql)
for _, word := range words {
result = append(result, word["word_id"])
}
return result
}