//GetTasks retrieves all the tasks depending on the //status pending or trashed or completed func GetTasks(username, status, category string) (types.Context, error) { log.Println("getting tasks for ", status) var tasks []types.Task var task types.Task var TaskCreated time.Time var context types.Context var getTaskSQL string var rows *sql.Rows comments, err := GetComments(username) if err != nil { return context, err } basicSQL := "select t.id, title, content, created_date, priority, case when c.name is null then 'NA' else c.name end from task t, status s, user u left outer join category c on c.id=t.cat_id where u.username=? and s.id=t.task_status_id and u.id=t.user_id " if category == "" { switch status { case "pending": getTaskSQL = basicSQL + " and s.status='PENDING' and t.hide!=1" case "deleted": getTaskSQL = basicSQL + " and s.status='DELETED' and t.hide!=1" case "completed": getTaskSQL = basicSQL + " and s.status='COMPLETE' and t.hide!=1" } getTaskSQL += " order by t.created_date asc" rows = database.query(getTaskSQL, username, username) } else { status = category //This is a special case for showing tasks with null categories, we do a union query if category == "UNCATEGORIZED" { getTaskSQL = "select t.id, title, content, created_date, priority, 'UNCATEGORIZED' from task t, status s, user u where u.username=? and s.id=t.task_status_id and u.id=t.user_id and t.cat_id=0 and s.status='PENDING' order by priority desc, created_date asc, finish_date asc" rows, err = database.db.Query(getTaskSQL, username) } else { getTaskSQL = basicSQL + " and name = ? and s.status='PENDING' order by priority desc, created_date asc, finish_date asc" rows, err = database.db.Query(getTaskSQL, username, category) } if err != nil { log.Println("tasks.go: something went wrong while getting query fetch tasks by category") } } defer rows.Close() for rows.Next() { task = types.Task{} err = rows.Scan(&task.Id, &task.Title, &task.Content, &TaskCreated, &task.Priority, &task.Category) taskCompleted := 0 totalTasks := 0 if strings.HasPrefix(task.Content, "- [") { for _, value := range strings.Split(task.Content, "\n") { if strings.HasPrefix(value, "- [x]") { taskCompleted += 1 } totalTasks += 1 } task.CompletedMsg = strconv.Itoa(taskCompleted) + " complete out of " + strconv.Itoa(totalTasks) } task.ContentHTML = template.HTML(md.Markdown([]byte(task.Content))) // TaskContent = strings.Replace(TaskContent, "\n", "<br>", -1) if err != nil { log.Println(err) } if comments[task.Id] != nil { task.Comments = comments[task.Id] } TaskCreated = TaskCreated.Local() // if task.Priority != "1" { // if priority is not 1 then calculate, else why bother? // CurrentTime := time.Now().Local() // diff := CurrentTime.Sub(TaskCreated).Hours() // if diff > 168 { // task.IsOverdue = true // If one week then overdue by default // } // } task.Created = TaskCreated.Format("Jan 2 2006") tasks = append(tasks, task) } context = types.Context{Tasks: tasks, Navigation: status} return context, nil }