//GetTasks retrieves all the tasks depending on the
//status pending or trashed or completed
func GetTasks(status, category string) (types.Context, error) {
var tasks []types.Task
var task types.Task
var TaskCreated time.Time
var context types.Context
var getTasksql string
var rows *sql.Rows
comments := GetComments()
basicSQL := "select id, title, content, created_date, priority from task t"
if status == "pending" && category == "" {
getTasksql = basicSQL + " where finish_date is null and is_deleted='N' order by priority desc, created_date asc"
} else if status == "deleted" {
getTasksql = basicSQL + " where is_deleted='Y' order by priority desc, created_date asc"
} else if status == "completed" {
getTasksql = basicSQL + " where finish_date is not null order by priority desc, created_date asc"
}
if category != "" {
status = category
getTasksql = "select t.id, title, content, created_date, priority from task t, category c where c.id = t.cat_id and name = ? and t.is_deleted!='Y' and t.finish_date is null order by priority desc, created_date asc, finish_date asc"
rows, err = database.db.Query(getTasksql, category)
if err != nil {
log.Println("something went wrong while getting query")
}
} else {
rows = database.query(getTasksql)
}
defer rows.Close()
for rows.Next() {
task = types.Task{}
err := rows.Scan(&task.Id, &task.Title, &task.Content, &TaskCreated, &task.Priority)
task.Content = string(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()
task.Created = TaskCreated.Format(time.UnixDate)[0:20]
tasks = append(tasks, task)
}
context = types.Context{Tasks: tasks, Navigation: status}
return context, nil
}
//SearchTask is used to return the search results depending on the query
func SearchTask(username, query string) (types.Context, error) {
var tasks []types.Task
var task types.Task
var TaskCreated time.Time
var context types.Context
comments, err := GetComments(username)
if err != nil {
log.Println("SearchTask: something went wrong in finding comments")
}
userID, err := GetUserID(username)
if err != nil {
return context, err
}
stmt := "select t.id, title, content, created_date, priority, c.name from task t, category c where t.user_id=? and c.id = t.cat_id and (title like '%" + query + "%' or content like '%" + query + "%') order by created_date desc"
rows := database.query(stmt, userID, query, query)
defer rows.Close()
for rows.Next() {
err := rows.Scan(&task.Id, &task.Title, &task.Content, &TaskCreated, &task.Priority, &task.Category)
if err != nil {
log.Println(err)
}
if comments[task.Id] != nil {
task.Comments = comments[task.Id]
}
task.Title = strings.Replace(task.Title, query, "<span class='highlight'>"+query+"</span>", -1)
task.Content = strings.Replace(task.Content, query, "<span class='highlight'>"+query+"</span>", -1)
task.Content = string(md.Markdown([]byte(task.Content)))
TaskCreated = TaskCreated.Local()
CurrentTime := time.Now().Local()
week := TaskCreated.AddDate(0, 0, 7)
if (week.String() < CurrentTime.String()) && (task.Priority != "1") {
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, Search: query, Navigation: "search"}
return context, nil
}
//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
}