111 lines
3.4 KiB
Python
111 lines
3.4 KiB
Python
from datetime import date, datetime
|
|
from typing import List
|
|
|
|
from sqlalchemy import func
|
|
from sqlalchemy.orm import Session
|
|
|
|
from app.crud.base import CRUDBase
|
|
from app.models.transaction import Transaction
|
|
from app.schemas.transaction import TransactionCreate, TransactionUpdate
|
|
|
|
|
|
class CRUDTransaction(CRUDBase[Transaction, TransactionCreate, TransactionUpdate]):
|
|
"""
|
|
CRUD operations for Transaction model.
|
|
"""
|
|
def create_with_item_and_user(
|
|
self, db: Session, *, obj_in: TransactionCreate, recorded_by_id: int
|
|
) -> Transaction:
|
|
"""
|
|
Create a new transaction record with item and user.
|
|
"""
|
|
obj_in_data = obj_in.model_dump()
|
|
db_obj = Transaction(**obj_in_data, recorded_by_id=recorded_by_id)
|
|
db.add(db_obj)
|
|
db.commit()
|
|
db.refresh(db_obj)
|
|
return db_obj
|
|
|
|
def get_by_item_id(
|
|
self, db: Session, *, item_id: int, skip: int = 0, limit: int = 100
|
|
) -> List[Transaction]:
|
|
"""
|
|
Get transactions by item ID.
|
|
"""
|
|
return (
|
|
db.query(Transaction)
|
|
.filter(Transaction.item_id == item_id)
|
|
.order_by(Transaction.transaction_date.desc())
|
|
.offset(skip)
|
|
.limit(limit)
|
|
.all()
|
|
)
|
|
|
|
def get_by_date_range(
|
|
self,
|
|
db: Session,
|
|
*,
|
|
start_date: date,
|
|
end_date: date = None,
|
|
skip: int = 0,
|
|
limit: int = 100
|
|
) -> List[Transaction]:
|
|
"""
|
|
Get transactions within a date range.
|
|
"""
|
|
if end_date is None:
|
|
end_date = date.today()
|
|
|
|
# Convert dates to datetime for proper comparison
|
|
start_datetime = datetime.combine(start_date, datetime.min.time())
|
|
end_datetime = datetime.combine(end_date, datetime.max.time())
|
|
|
|
return (
|
|
db.query(Transaction)
|
|
.filter(Transaction.transaction_date >= start_datetime)
|
|
.filter(Transaction.transaction_date <= end_datetime)
|
|
.order_by(Transaction.transaction_date.desc())
|
|
.offset(skip)
|
|
.limit(limit)
|
|
.all()
|
|
)
|
|
|
|
def get_summary_by_type(
|
|
self, db: Session, *, start_date: date, end_date: date = None
|
|
) -> List[dict]:
|
|
"""
|
|
Get transaction summary aggregated by transaction type.
|
|
"""
|
|
if end_date is None:
|
|
end_date = date.today()
|
|
|
|
# Convert dates to datetime for proper comparison
|
|
start_datetime = datetime.combine(start_date, datetime.min.time())
|
|
end_datetime = datetime.combine(end_date, datetime.max.time())
|
|
|
|
result = (
|
|
db.query(
|
|
Transaction.transaction_type,
|
|
func.count().label("count"),
|
|
func.sum(Transaction.quantity).label("total_quantity"),
|
|
func.sum(Transaction.unit_price * Transaction.quantity).label("total_value")
|
|
)
|
|
.filter(Transaction.transaction_date >= start_datetime)
|
|
.filter(Transaction.transaction_date <= end_datetime)
|
|
.group_by(Transaction.transaction_type)
|
|
.all()
|
|
)
|
|
|
|
return [
|
|
{
|
|
"transaction_type": r[0],
|
|
"count": r[1],
|
|
"total_quantity": r[2],
|
|
"total_value": r[3]
|
|
}
|
|
for r in result
|
|
]
|
|
|
|
|
|
# Create a singleton instance
|
|
transaction = CRUDTransaction(Transaction) |