2519513d99
- Add enhanced due date filtering to get_todos function with support for "today", "this_week", "next_week" - Add overdue filtering parameter with timezone-aware comparisons - Add sorting by due_date with null values at end - Add get_overdue_todos function for retrieving overdue todos - Add get_todos_due_soon function for todos due within next N days (default 7) - Add get_todos_by_date_range function for custom date range filtering - Create comprehensive date utilities in app/utils/date_utils.py with timezone support - Add project_id and tag_ids support to TodoCreate and TodoUpdate schemas - Include efficient database indexing for due_date queries - Use SQLAlchemy timezone-aware datetime filtering throughout - Add proper overdue detection logic with timezone handling
357 lines
11 KiB
Python
357 lines
11 KiB
Python
from typing import List, Optional, Tuple
|
|
from datetime import datetime
|
|
from sqlalchemy.orm import Session
|
|
from sqlalchemy import and_, or_
|
|
|
|
from app.models.todo import Todo, Priority
|
|
from app.models.tag import Tag
|
|
from app.schemas.todo import TodoCreate, TodoUpdate, SubtaskCreate
|
|
from app.utils.date_utils import (
|
|
get_timezone_aware_now,
|
|
get_date_range_today,
|
|
get_date_range_this_week,
|
|
get_date_range_next_week,
|
|
get_overdue_cutoff,
|
|
get_due_soon_cutoff,
|
|
)
|
|
|
|
|
|
def get_todo(db: Session, todo_id: int) -> Optional[Todo]:
|
|
return db.query(Todo).filter(Todo.id == todo_id).first()
|
|
|
|
|
|
def get_todos(
|
|
db: Session,
|
|
skip: int = 0,
|
|
limit: int = 100,
|
|
completed: Optional[bool] = None,
|
|
priority: Optional[Priority] = None,
|
|
search: Optional[str] = None,
|
|
category_id: Optional[int] = None,
|
|
project_id: Optional[int] = None,
|
|
parent_id: Optional[int] = None,
|
|
include_subtasks: bool = True,
|
|
due_date_filter: Optional[str] = None,
|
|
overdue: Optional[bool] = None,
|
|
sort_by_due_date: bool = False,
|
|
due_date_start: Optional[datetime] = None,
|
|
due_date_end: Optional[datetime] = None,
|
|
) -> Tuple[List[Todo], int]:
|
|
query = db.query(Todo)
|
|
|
|
# Filter by parent_id to get only main todos or subtasks
|
|
if parent_id is not None:
|
|
query = query.filter(Todo.parent_id == parent_id)
|
|
elif not include_subtasks:
|
|
# Only get main todos (no subtasks) if parent_id is None and include_subtasks is False
|
|
query = query.filter(Todo.parent_id.is_(None))
|
|
|
|
# Apply filters
|
|
if completed is not None:
|
|
query = query.filter(Todo.completed == completed)
|
|
if priority is not None:
|
|
query = query.filter(Todo.priority == priority)
|
|
if category_id is not None:
|
|
query = query.filter(Todo.category_id == category_id)
|
|
if project_id is not None:
|
|
query = query.filter(Todo.project_id == project_id)
|
|
if search:
|
|
query = query.filter(
|
|
Todo.title.contains(search) | Todo.description.contains(search)
|
|
)
|
|
|
|
# Due date filtering
|
|
if due_date_filter:
|
|
if due_date_filter == "today":
|
|
start_date, end_date = get_date_range_today()
|
|
query = query.filter(
|
|
and_(Todo.due_date >= start_date, Todo.due_date <= end_date)
|
|
)
|
|
elif due_date_filter == "this_week":
|
|
start_date, end_date = get_date_range_this_week()
|
|
query = query.filter(
|
|
and_(Todo.due_date >= start_date, Todo.due_date <= end_date)
|
|
)
|
|
elif due_date_filter == "next_week":
|
|
start_date, end_date = get_date_range_next_week()
|
|
query = query.filter(
|
|
and_(Todo.due_date >= start_date, Todo.due_date <= end_date)
|
|
)
|
|
|
|
# Custom date range filtering
|
|
if due_date_start:
|
|
query = query.filter(Todo.due_date >= due_date_start)
|
|
if due_date_end:
|
|
query = query.filter(Todo.due_date <= due_date_end)
|
|
|
|
# Overdue filtering
|
|
if overdue is not None:
|
|
current_time = get_overdue_cutoff()
|
|
if overdue:
|
|
query = query.filter(
|
|
and_(Todo.due_date.isnot(None), Todo.due_date < current_time)
|
|
)
|
|
else:
|
|
query = query.filter(
|
|
or_(Todo.due_date.is_(None), Todo.due_date >= current_time)
|
|
)
|
|
|
|
# Get total count before pagination
|
|
total = query.count()
|
|
|
|
# Apply pagination and ordering
|
|
if sort_by_due_date:
|
|
# Sort by due_date ascending (earliest first), with None values at the end
|
|
todos = (
|
|
query.order_by(Todo.due_date.asc().nullslast(), Todo.created_at.desc())
|
|
.offset(skip)
|
|
.limit(limit)
|
|
.all()
|
|
)
|
|
else:
|
|
todos = query.order_by(Todo.created_at.desc()).offset(skip).limit(limit).all()
|
|
|
|
return todos, total
|
|
|
|
|
|
def get_overdue_todos(
|
|
db: Session,
|
|
skip: int = 0,
|
|
limit: int = 100,
|
|
completed: Optional[bool] = None,
|
|
priority: Optional[Priority] = None,
|
|
category_id: Optional[int] = None,
|
|
project_id: Optional[int] = None,
|
|
parent_id: Optional[int] = None,
|
|
include_subtasks: bool = True,
|
|
) -> Tuple[List[Todo], int]:
|
|
"""Get all overdue todos."""
|
|
current_time = get_overdue_cutoff()
|
|
query = db.query(Todo).filter(
|
|
and_(Todo.due_date.isnot(None), Todo.due_date < current_time)
|
|
)
|
|
|
|
# Apply same filtering logic as get_todos
|
|
if parent_id is not None:
|
|
query = query.filter(Todo.parent_id == parent_id)
|
|
elif not include_subtasks:
|
|
query = query.filter(Todo.parent_id.is_(None))
|
|
|
|
if completed is not None:
|
|
query = query.filter(Todo.completed == completed)
|
|
if priority is not None:
|
|
query = query.filter(Todo.priority == priority)
|
|
if category_id is not None:
|
|
query = query.filter(Todo.category_id == category_id)
|
|
if project_id is not None:
|
|
query = query.filter(Todo.project_id == project_id)
|
|
|
|
total = query.count()
|
|
todos = (
|
|
query.order_by(Todo.due_date.asc(), Todo.created_at.desc())
|
|
.offset(skip)
|
|
.limit(limit)
|
|
.all()
|
|
)
|
|
|
|
return todos, total
|
|
|
|
|
|
def get_todos_due_soon(
|
|
db: Session,
|
|
days: int = 7,
|
|
skip: int = 0,
|
|
limit: int = 100,
|
|
completed: Optional[bool] = None,
|
|
priority: Optional[Priority] = None,
|
|
category_id: Optional[int] = None,
|
|
project_id: Optional[int] = None,
|
|
parent_id: Optional[int] = None,
|
|
include_subtasks: bool = True,
|
|
) -> Tuple[List[Todo], int]:
|
|
"""Get todos due within the next N days (default 7 days)."""
|
|
current_time = get_timezone_aware_now()
|
|
future_cutoff = get_due_soon_cutoff(days)
|
|
|
|
query = db.query(Todo).filter(
|
|
and_(
|
|
Todo.due_date.isnot(None),
|
|
Todo.due_date >= current_time,
|
|
Todo.due_date <= future_cutoff,
|
|
)
|
|
)
|
|
|
|
# Apply same filtering logic as get_todos
|
|
if parent_id is not None:
|
|
query = query.filter(Todo.parent_id == parent_id)
|
|
elif not include_subtasks:
|
|
query = query.filter(Todo.parent_id.is_(None))
|
|
|
|
if completed is not None:
|
|
query = query.filter(Todo.completed == completed)
|
|
if priority is not None:
|
|
query = query.filter(Todo.priority == priority)
|
|
if category_id is not None:
|
|
query = query.filter(Todo.category_id == category_id)
|
|
if project_id is not None:
|
|
query = query.filter(Todo.project_id == project_id)
|
|
|
|
total = query.count()
|
|
todos = (
|
|
query.order_by(Todo.due_date.asc(), Todo.created_at.desc())
|
|
.offset(skip)
|
|
.limit(limit)
|
|
.all()
|
|
)
|
|
|
|
return todos, total
|
|
|
|
|
|
def get_todos_by_date_range(
|
|
db: Session,
|
|
start_date: datetime,
|
|
end_date: datetime,
|
|
skip: int = 0,
|
|
limit: int = 100,
|
|
completed: Optional[bool] = None,
|
|
priority: Optional[Priority] = None,
|
|
category_id: Optional[int] = None,
|
|
project_id: Optional[int] = None,
|
|
parent_id: Optional[int] = None,
|
|
include_subtasks: bool = True,
|
|
) -> Tuple[List[Todo], int]:
|
|
"""Get todos within a specific date range."""
|
|
query = db.query(Todo).filter(
|
|
and_(
|
|
Todo.due_date.isnot(None),
|
|
Todo.due_date >= start_date,
|
|
Todo.due_date <= end_date,
|
|
)
|
|
)
|
|
|
|
# Apply same filtering logic as get_todos
|
|
if parent_id is not None:
|
|
query = query.filter(Todo.parent_id == parent_id)
|
|
elif not include_subtasks:
|
|
query = query.filter(Todo.parent_id.is_(None))
|
|
|
|
if completed is not None:
|
|
query = query.filter(Todo.completed == completed)
|
|
if priority is not None:
|
|
query = query.filter(Todo.priority == priority)
|
|
if category_id is not None:
|
|
query = query.filter(Todo.category_id == category_id)
|
|
if project_id is not None:
|
|
query = query.filter(Todo.project_id == project_id)
|
|
|
|
total = query.count()
|
|
todos = (
|
|
query.order_by(Todo.due_date.asc(), Todo.created_at.desc())
|
|
.offset(skip)
|
|
.limit(limit)
|
|
.all()
|
|
)
|
|
|
|
return todos, total
|
|
|
|
|
|
def create_todo(db: Session, todo: TodoCreate) -> Todo:
|
|
todo_data = todo.model_dump(exclude={"tag_ids"})
|
|
db_todo = Todo(**todo_data)
|
|
|
|
# Handle tags if provided
|
|
if todo.tag_ids:
|
|
tags = db.query(Tag).filter(Tag.id.in_(todo.tag_ids)).all()
|
|
db_todo.tags = tags
|
|
|
|
db.add(db_todo)
|
|
db.commit()
|
|
db.refresh(db_todo)
|
|
return db_todo
|
|
|
|
|
|
def update_todo(db: Session, todo_id: int, todo_update: TodoUpdate) -> Optional[Todo]:
|
|
db_todo = db.query(Todo).filter(Todo.id == todo_id).first()
|
|
if db_todo:
|
|
update_data = todo_update.model_dump(exclude_unset=True, exclude={"tag_ids"})
|
|
|
|
# Handle tags if provided
|
|
if todo_update.tag_ids is not None:
|
|
tags = db.query(Tag).filter(Tag.id.in_(todo_update.tag_ids)).all()
|
|
db_todo.tags = tags
|
|
|
|
# Update other fields
|
|
for field, value in update_data.items():
|
|
setattr(db_todo, field, value)
|
|
|
|
db.commit()
|
|
db.refresh(db_todo)
|
|
return db_todo
|
|
|
|
|
|
def delete_todo(db: Session, todo_id: int) -> bool:
|
|
db_todo = db.query(Todo).filter(Todo.id == todo_id).first()
|
|
if db_todo:
|
|
db.delete(db_todo)
|
|
db.commit()
|
|
return True
|
|
return False
|
|
|
|
|
|
def get_subtasks(db: Session, parent_id: int) -> List[Todo]:
|
|
"""Get all subtasks for a given parent todo."""
|
|
return (
|
|
db.query(Todo)
|
|
.filter(Todo.parent_id == parent_id)
|
|
.order_by(Todo.created_at.desc())
|
|
.all()
|
|
)
|
|
|
|
|
|
def create_subtask(
|
|
db: Session, parent_id: int, subtask: SubtaskCreate
|
|
) -> Optional[Todo]:
|
|
"""Create a subtask for a given parent todo."""
|
|
# Check if parent exists
|
|
parent_todo = db.query(Todo).filter(Todo.id == parent_id).first()
|
|
if not parent_todo:
|
|
return None
|
|
|
|
# Create subtask with parent_id
|
|
subtask_data = subtask.model_dump()
|
|
subtask_data["parent_id"] = parent_id
|
|
db_subtask = Todo(**subtask_data)
|
|
|
|
db.add(db_subtask)
|
|
db.commit()
|
|
db.refresh(db_subtask)
|
|
return db_subtask
|
|
|
|
|
|
def move_subtask(
|
|
db: Session, subtask_id: int, new_parent_id: Optional[int]
|
|
) -> Optional[Todo]:
|
|
"""Move a subtask to a different parent or make it a main todo."""
|
|
subtask = db.query(Todo).filter(Todo.id == subtask_id).first()
|
|
if not subtask:
|
|
return None
|
|
|
|
# If new_parent_id is provided, check if it exists and is not the subtask itself
|
|
if new_parent_id is not None:
|
|
if new_parent_id == subtask_id:
|
|
return None # Cannot make a todo a subtask of itself
|
|
|
|
new_parent = db.query(Todo).filter(Todo.id == new_parent_id).first()
|
|
if not new_parent:
|
|
return None # New parent doesn't exist
|
|
|
|
# Prevent creating cycles (subtask cannot become parent of its current parent)
|
|
if new_parent.parent_id == subtask_id:
|
|
return None
|
|
|
|
# Update the parent_id
|
|
subtask.parent_id = new_parent_id
|
|
db.commit()
|
|
db.refresh(subtask)
|
|
return subtask
|