func CreateProject(params map[string]string) (int64, error) {
const rawSQL = `
INSERT INTO project (author_id, title, tagline, description, image_url, view_count, status, updated_at, created_at)
VALUES ($1, $2, $3, $4, '', 0, 'concept', now(), now())
RETURNING id`
var parser Parser
authorID := parser.Int(params["authorID"])
if parser.Err != nil {
debug.Error(parser.Err)
return 0, parser.Err
}
title := params["title"]
tagline := params["tagline"]
description := params["description"]
var id int64
if err := db.QueryRow(rawSQL, authorID, title, tagline, description).Scan(&id); err != nil {
debug.Error(err)
return 0, err
}
return id, nil
}
func CreateTask(params CreateTaskParams) (int64, error) {
const rawSQL = `
INSERT INTO task (author_id, project_id, title, description, done,
start_date, end_date, updated_at, created_at)
VALUES ($1, $2, $3, $4, $5, $6, $7, now(), now())
RETURNING id`
var id int64
if err := db.QueryRow(
rawSQL,
params.AuthorID,
params.ProjectID,
params.Title,
params.Description,
params.Done,
params.StartDate,
params.EndDate,
).Scan(&id); err != nil {
debug.Error(err)
return 0, err
}
if err := UpdateTaskTags(id, params.Tags); err != nil {
debug.Error(err)
return 0, err
}
return id, nil
}
func GetFriendIDs(userID int64) ([]int64, error) {
const rawSQL = `
SELECT user_.id FROM user_
INNER JOIN friend ON user_.id = friend.user1_id
WHERE user_.id != $1`
rows, err := db.Query(rawSQL, userID)
if err != nil {
debug.Error(err)
return nil, err
}
defer rows.Close()
var ids []int64
for rows.Next() {
var id int64
if err = rows.Scan(&id); err != nil && err != sql.ErrNoRows {
debug.Error(err)
return ids, err
}
ids = append(ids, id)
}
return ids, nil
}
func queryChats(rawSQL string, data ...interface{}) ([]Chat, error) {
rows, err := db.Query(rawSQL, data...)
if err != nil {
debug.Error(err)
return nil, err
}
defer rows.Close()
var cs []Chat
for rows.Next() {
var c Chat
if err = rows.Scan(
&c.ID_,
&c.UserID_,
&c.ChannelID_,
&c.ChannelType_,
&c.Text_,
&c.CreatedAt_,
&c.Username_,
); err != nil && err != sql.ErrNoRows {
debug.Error(err)
return nil, err
}
cs = append(cs, c)
}
return cs, nil
}
func UpdateTaskTags(taskID int64, tags []string) error {
const rawSQL = `
INSERT INTO task_tag VALUES ($1, (SELECT id FROM tag WHERE name = $2))`
if err := ClearTaskTags(taskID); err != nil {
debug.Error(err)
return err
}
for _, tag := range tags {
if yes, err := TagExists(tag); err != nil {
debug.Error(err)
return err
} else if !yes {
if err := CreateTag(tag); err != nil {
debug.Error(err)
return err
}
}
if _, err := db.Exec(rawSQL, taskID, tag); err != nil {
debug.Error(err)
return err
}
}
return nil
}
func DeleteProject(projectID int64) error {
const rawSQL = `DELETE FROM project WHERE id = $1`
// delete project
if _, err := db.Exec(rawSQL, projectID); err != nil {
debug.Error(err)
return err
}
const rawSQL2 = `DELETE FROM member WHERE project_id = $1`
// delete project users
if _, err := db.Exec(rawSQL2, projectID); err != nil {
debug.Error(err)
return err
}
// delete project image
if err := os.RemoveAll(fmt.Sprintf(path.Base(ProjectImageURL), projectID)); err != nil {
debug.Error(err)
return err
}
return nil
}
func queryTasks(rawSQL string, data ...interface{}) ([]Task, error) {
rows, err := db.Query(rawSQL, data...)
if err != nil {
debug.Error(err)
return nil, err
}
defer rows.Close()
var ts []Task
for rows.Next() {
var t task
if err = rows.Scan(
&t.ID,
&t.AuthorID,
&t.ProjectID,
&t.Title,
&t.Description,
&t.Done,
&t.StartDate,
&t.EndDate,
&t.UpdatedAt,
&t.CreatedAt,
); err != nil && err != sql.ErrNoRows {
debug.Error(err)
return nil, err
}
if t.Author, err = GetUser(t.AuthorID); err != nil {
debug.Error(err)
return nil, err
}
t.StartDateStr = t.StartDate.Format("02 January, 2006")
t.EndDateStr = t.EndDate.Format("02 January, 2006")
if t.Workers, err = GetWorkers(t.ID); err != nil {
debug.Error(err)
return nil, err
}
if t.Tags, err = TaskTags(t.ID); err != nil {
debug.Error(err)
return nil, err
}
ts = append(ts, t)
}
return ts, nil
}
func queryProjects(rawSQL string, data ...interface{}) ([]Project, error) {
rows, err := db.Query(rawSQL, data...)
if err != nil {
debug.Error(err)
return nil, err
}
defer rows.Close()
var ps []Project
for rows.Next() {
var p Project
if err = rows.Scan(
&p.ID,
&p.AuthorID,
&p.Title,
&p.Tagline,
&p.Description,
&p.ImageURL,
&p.ViewCount,
&p.Status,
&p.UpdatedAt,
&p.CreatedAt,
); err != nil {
debug.Error(err)
return nil, err
}
if p.Tasks, err = GetTasks(p.ID); err != nil {
debug.Error(err)
return ps, err
}
if p.Milestones, err = GetMilestones(p.ID); err != nil {
debug.Error(err)
return ps, err
}
if p.Members, err = GetMembers(p.ID); err != nil {
debug.Error(err)
return ps, err
}
ps = append(ps, p)
}
return ps, nil
}
func ValidLogin(email, password string) (bool, error) {
const q = `SELECT * FROM user_ WHERE email = $1`
var u user
if err := db.QueryRow(q, email).Scan(
&u.ID_,
&u.Email,
&u.Password,
&u.Fullname,
&u.Title,
&u.Description,
&u.AvatarURL,
&u.VerificationCode,
&u.IsAdmin_,
&u.UpdatedAt,
&u.CreatedAt,
); err != nil {
if err != sql.ErrNoRows {
debug.Error(err)
return false, err
} else {
err = nil
}
}
if bcrypt.CompareHashAndPassword([]byte(u.Password), []byte(password)) != nil || u.VerificationCode != "verified" {
return false, nil
}
return true, nil
}
func queryMilestones(rawSQL string, data ...interface{}) ([]Milestone, error) {
rows, err := db.Query(rawSQL, data...)
if err != nil {
return nil, err
}
defer rows.Close()
var ms []Milestone
for rows.Next() {
var m milestone
if err = rows.Scan(
&m.ID_,
&m.ProjectID_,
&m.Title_,
&m.Description_,
&m.Date_,
&m.UpdatedAt_,
&m.CreatedAt_,
); err != nil && err != sql.ErrNoRows {
debug.Error(err)
return nil, err
}
m.DateStr = m.Date_.Format("02 January, 2006")
ms = append(ms, m)
}
return ms, nil
}
func updateProjectImage(projectID int64, imageURL string) error {
const rawSQL = `UPDATE project SET image_url = $1 WHERE id = $2`
if _, err := db.Exec(rawSQL, imageURL, projectID); err != nil {
debug.Error(err)
return err
}
return nil
}
func NotifyChat(params NotifyChatParams) error {
extra := fmt.Sprintf("c/%d/%d/%d/%s", params.ID, params.UserID, params.ChannelID, params.ChannelType)
if _, err := db.Exec(`NOTIFY chat, '` + extra + `'`); err != nil {
debug.Error(err)
return err
}
return nil
}
func ToggleTaskStatus(taskID int64) error {
const rawSQL = `UPDATE task SET done = not done WHERE id = $1`
if _, err := db.Exec(rawSQL, taskID); err != nil {
debug.Error(err)
return err
}
return nil
}
func UpdateProjectDescription(projectID int64, description string) error {
const rawSQL = `UPDATE project SET description = $1, updated_at = now() WHERE id = $2`
if _, err := db.Exec(rawSQL, description, projectID); err != nil {
debug.Error(err)
return err
}
return nil
}
func UpdateProjectTagline(projectID int64, tagline string) error {
const rawSQL = `UPDATE project SET tagline = $1, updated_at = now() WHERE id = $2`
if _, err := db.Exec(rawSQL, tagline, projectID); err != nil {
debug.Error(err)
return err
}
return nil
}
func queryTags(q string, data ...interface{}) ([]string, error) {
rows, err := db.Query(q, data...)
if err != nil {
debug.Error(err)
return nil, err
}
defer rows.Close()
var ts []string
for rows.Next() {
var t string
if err = rows.Scan(&t); err != nil {
debug.Error(err)
return ts, err
}
ts = append(ts, t)
}
return ts, nil
}
func SaveProjectImage(w http.ResponseWriter, r *http.Request, projectID int64) (bool, error) {
url := fmt.Sprintf(ProjectImageURL, projectID)
finalURL, header, err := httputil.SaveFileWithExtension(w, r, "image", url)
if err != nil || header == nil {
if err := os.Chdir(".."); err != nil {
debug.Error(err)
return false, err
}
return false, nil
}
if err := updateProjectImage(projectID, finalURL); err != nil {
debug.Error(err)
return false, err
}
return true, nil
}
func GetTask(taskID int64) (Task, error) {
var t task
var err error
const rawSQL = `
SELECT * FROM task WHERE id = $1`
if err = db.QueryRow(rawSQL, taskID).Scan(
&t.ID,
&t.AuthorID,
&t.ProjectID,
&t.Title,
&t.Description,
&t.Done,
&t.StartDate,
&t.EndDate,
&t.UpdatedAt,
&t.CreatedAt,
); err != nil && err != sql.ErrNoRows {
debug.Error(err)
return nil, err
}
if t.Author, err = GetUser(t.AuthorID); err != nil {
debug.Error(err)
return nil, err
}
t.StartDateStr = t.StartDate.Format("02 Jan, 2006")
t.EndDateStr = t.EndDate.Format("02 Jan, 2006")
if t.Workers, err = GetWorkers(t.ID); err != nil {
debug.Error(err)
return nil, err
}
if t.Tags, err = TaskTags(t.ID); err != nil {
debug.Error(err)
return nil, err
}
return t, nil
}
func UnsetFeaturedProject(projectID int64) error {
const rawSQL = `
DELETE FROM featured_project WHERE project_id = $1`
if _, err := db.Exec(rawSQL, projectID); err != nil {
debug.Error(err)
return err
}
return nil
}
func SetFeaturedProject(projectID int64) error {
const rawSQL = `
INSERT INTO featured_project (project_id, created_at) VALUES ($1, now())`
if _, err := db.Exec(rawSQL, projectID); err != nil {
debug.Error(err)
return err
}
return nil
}
func CreateTag(name string) error {
const rawSQL = `
INSERT INTO tag (name) VALUES ($1)`
if _, err := db.Exec(rawSQL, name); err != nil {
debug.Error(err)
return err
}
return nil
}
func RemoveMember(projectID, userID int64) error {
const rawSQL = `
DELETE FROM member WHERE project_id = $1 AND user_id = $2`
if _, err := db.Exec(rawSQL, projectID, userID); err != nil {
debug.Error(err)
return err
}
return nil
}
func AddMember(projectID, userID int64) error {
const rawSQL = `
INSERT INTO member (project_id, user_id) VALUES ($1, $2)`
if _, err := db.Exec(rawSQL, projectID, userID); err != nil {
debug.Error(err)
return err
}
return nil
}
func DeleteWorkers(taskID int64) error {
const rawSQL = `
DELETE FROM worker WHERE task_id = $1`
if _, err := db.Exec(rawSQL, taskID); err != nil {
debug.Error(err)
return err
}
return nil
}
func ClearUserTags(userID int64) error {
const rawSQL = `
DELETE FROM user_tag WHERE user_id = $1`
if _, err := db.Exec(rawSQL, userID); err != nil {
debug.Error(err)
return err
}
return nil
}
func DeleteMilestone(milestoneID int64) error {
const rawSQL = `
DELETE FROM milestone WHERE id = $1`
if _, err := db.Exec(rawSQL, milestoneID); err != nil {
debug.Error(err)
return err
}
return nil
}
func RemoveTag(name string) error {
const rawSQL = `
DELETE FROM tag WHERE name = $1`
if _, err := db.Exec(rawSQL, name); err != nil {
debug.Error(err)
return err
}
return nil
}
func ClearTaskTags(taskID int64) error {
const rawSQL = `
DELETE FROM task_tag WHERE task_id = $1`
if _, err := db.Exec(rawSQL, taskID); err != nil {
debug.Error(err)
return err
}
return nil
}
func AddFriend(userID, otherUserID int64) error {
const rawSQL = `
INSERT INTO friend
VALUES ($1, $2), ($3, $4)`
if _, err := db.Exec(rawSQL, userID, otherUserID, otherUserID, userID); err != nil {
debug.Error(err)
return err
}
return nil
}
func UpdateTask(params UpdateTaskParams) error {
const rawSQL = `
UPDATE task SET title = $1,
description = $2,
start_date = $3,
end_date = $4,
updated_at = now() WHERE id = $5`
var parser Parser
taskID := parser.Int(params.TaskID)
startDate := parser.Time(params.StartDate)
endDate := parser.Time(params.EndDate)
if parser.Err != nil {
debug.Error(parser.Err)
return parser.Err
}
title := params.Title
description := params.Description
if _, err := db.Exec(
rawSQL,
title,
description,
startDate,
endDate,
taskID,
); err != nil {
debug.Error(err)
return err
}
if err := UpdateTaskTags(taskID, params.Tags); err != nil {
debug.Error(err)
return err
}
return nil
}