Beispiel #1
0
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
}
Beispiel #2
0
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
}
Beispiel #3
0
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
}
Beispiel #4
0
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
}
Beispiel #6
0
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
}
Beispiel #7
0
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
}
Beispiel #8
0
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
}
Beispiel #9
0
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
}
Beispiel #11
0
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
}
Beispiel #12
0
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
}
Beispiel #13
0
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
}
Beispiel #14
0
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
}
Beispiel #15
0
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
}
Beispiel #16
0
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
}
Beispiel #17
0
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
}
Beispiel #18
0
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
}
Beispiel #19
0
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
}
Beispiel #20
0
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
}
Beispiel #21
0
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
}
Beispiel #22
0
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
}
Beispiel #23
0
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
}
Beispiel #24
0
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
}
Beispiel #25
0
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
}
Beispiel #27
0
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
}
Beispiel #28
0
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
}
Beispiel #29
0
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
}
Beispiel #30
0
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
}