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 }