Spaces:
Sleeping
Sleeping
apis for generate p&l
#9
by
praneethys
- opened
- Makefile +6 -0
- README.md +2 -2
- app/api/routers/income_statement.py +49 -0
- app/model/income_statement.py +36 -0
- app/model/transaction.py +9 -1
- app/model/user.py +1 -0
- app/schema/index.py +14 -0
- app/service/income_statement.py +15 -0
- main.py +9 -5
- migration/env.py +4 -3
- migration/versions/4e76691ab103_add_income_statement_table.py +43 -0
Makefile
CHANGED
@@ -6,3 +6,9 @@ build-postgres:
|
|
6 |
|
7 |
run-postgres:
|
8 |
docker run -d -p 5432:5432 -e POSTGRES_PASSWORD=${POSTGRES_PASSWORD} -e POSTGRES_USER=${POSTGRES_USER} --name ${POSTGRES_DB_NAME} codepath_project_postgres
|
|
|
|
|
|
|
|
|
|
|
|
|
|
6 |
|
7 |
run-postgres:
|
8 |
docker run -d -p 5432:5432 -e POSTGRES_PASSWORD=${POSTGRES_PASSWORD} -e POSTGRES_USER=${POSTGRES_USER} --name ${POSTGRES_DB_NAME} codepath_project_postgres
|
9 |
+
|
10 |
+
run-migrations:
|
11 |
+
alembic upgrade head
|
12 |
+
|
13 |
+
generate-migrations:
|
14 |
+
alembic revision --autogenerate -m "$(migration_title)"
|
README.md
CHANGED
@@ -90,13 +90,13 @@ make run-postgres
|
|
90 |
To generate new migrations, run:
|
91 |
|
92 |
```bash
|
93 |
-
|
94 |
```
|
95 |
|
96 |
To locally verify your changes, run:
|
97 |
|
98 |
```bash
|
99 |
-
|
100 |
```
|
101 |
|
102 |
## Using Docker
|
|
|
90 |
To generate new migrations, run:
|
91 |
|
92 |
```bash
|
93 |
+
make generate-migrations migration_title="<name_for_migration>"
|
94 |
```
|
95 |
|
96 |
To locally verify your changes, run:
|
97 |
|
98 |
```bash
|
99 |
+
make run-migrations
|
100 |
```
|
101 |
|
102 |
## Using Docker
|
app/api/routers/income_statement.py
ADDED
@@ -0,0 +1,49 @@
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1 |
+
from typing import List
|
2 |
+
from fastapi import APIRouter, Depends, HTTPException, status
|
3 |
+
from sqlalchemy.ext.asyncio import AsyncSession
|
4 |
+
from app.model.transaction import Transaction as TransactionModel
|
5 |
+
from app.model.income_statement import IncomeStatement as IncomeStatementModel
|
6 |
+
from app.schema.index import IncomeStatementCreate, IncomeStatementResponse
|
7 |
+
from app.engine.postgresdb import get_db_session
|
8 |
+
from app.service.income_statement import call_llm_to_create_income_statement
|
9 |
+
|
10 |
+
income_statement_router = r = APIRouter(prefix="/api/v1/income_statement", tags=["income_statement"])
|
11 |
+
|
12 |
+
|
13 |
+
@r.post(
|
14 |
+
"/",
|
15 |
+
responses={
|
16 |
+
200: {"description": "New transaction created"},
|
17 |
+
400: {"description": "Bad request"},
|
18 |
+
500: {"description": "Internal server error"},
|
19 |
+
},
|
20 |
+
)
|
21 |
+
async def create_income_statement(payload: IncomeStatementCreate, db: AsyncSession = Depends(get_db_session)) -> None:
|
22 |
+
try:
|
23 |
+
await call_llm_to_create_income_statement(payload, db)
|
24 |
+
|
25 |
+
except Exception as e:
|
26 |
+
raise HTTPException(status_code=500, detail=str(e))
|
27 |
+
|
28 |
+
|
29 |
+
@r.get(
|
30 |
+
"/{user_id}",
|
31 |
+
response_model=List[IncomeStatementResponse],
|
32 |
+
responses={
|
33 |
+
200: {"description": "New user created"},
|
34 |
+
400: {"description": "Bad request"},
|
35 |
+
204: {"description": "No content"},
|
36 |
+
500: {"description": "Internal server error"},
|
37 |
+
},
|
38 |
+
)
|
39 |
+
async def get_income_statements(
|
40 |
+
user_id: int, db: AsyncSession = Depends(get_db_session)
|
41 |
+
) -> List[IncomeStatementResponse]:
|
42 |
+
"""
|
43 |
+
Retrieve all income statements.
|
44 |
+
"""
|
45 |
+
result = await IncomeStatementModel.get_by_user(db, user_id)
|
46 |
+
all_rows = result.all()
|
47 |
+
if len(all_rows) == 0:
|
48 |
+
raise HTTPException(status_code=status.HTTP_204_NO_CONTENT, detail="No income statements found for this user")
|
49 |
+
return all_rows
|
app/model/income_statement.py
ADDED
@@ -0,0 +1,36 @@
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1 |
+
from datetime import datetime
|
2 |
+
from typing import Any, Dict, List
|
3 |
+
from sqlalchemy import ForeignKey
|
4 |
+
from sqlalchemy.sql import expression as sql
|
5 |
+
from sqlalchemy.orm import relationship, Mapped, mapped_column
|
6 |
+
from sqlalchemy.ext.asyncio import AsyncSession
|
7 |
+
from sqlalchemy.dialects.postgresql import JSON
|
8 |
+
|
9 |
+
from app.model.base import BaseModel
|
10 |
+
from app.engine.postgresdb import Base
|
11 |
+
|
12 |
+
|
13 |
+
class IncomeStatement(Base, BaseModel):
|
14 |
+
__tablename__ = "income_statement"
|
15 |
+
|
16 |
+
date_from: Mapped[datetime]
|
17 |
+
date_to: Mapped[datetime]
|
18 |
+
income: Mapped[Dict] = mapped_column(JSON, nullable=True)
|
19 |
+
expenses: Mapped[Dict] = mapped_column(JSON, nullable=True)
|
20 |
+
|
21 |
+
user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
|
22 |
+
user = relationship("User", back_populates="income_statements")
|
23 |
+
|
24 |
+
@classmethod
|
25 |
+
def create(cls: "type[IncomeStatement]", db: AsyncSession, **kwargs) -> "IncomeStatement":
|
26 |
+
query = sql.insert(cls).values(**kwargs)
|
27 |
+
income_statements = db.execute(query)
|
28 |
+
income_statement = income_statements.first()
|
29 |
+
db.commit()
|
30 |
+
return income_statement
|
31 |
+
|
32 |
+
@classmethod
|
33 |
+
async def get_by_user(cls: "type[IncomeStatement]", db: AsyncSession, user_id: int) -> "List[IncomeStatement]":
|
34 |
+
query = sql.select(cls).where(cls.user_id == user_id)
|
35 |
+
income_statements = await db.scalars(query)
|
36 |
+
return income_statements
|
app/model/transaction.py
CHANGED
@@ -23,7 +23,7 @@ class Transaction(Base, BaseModel):
|
|
23 |
|
24 |
@classmethod
|
25 |
async def create(cls: "type[Transaction]", db: AsyncSession, **kwargs) -> "Transaction":
|
26 |
-
query = sql.insert(cls).values(**kwargs)
|
27 |
transactions = await db.execute(query)
|
28 |
transaction = transactions.first()
|
29 |
await db.commit()
|
@@ -48,3 +48,11 @@ class Transaction(Base, BaseModel):
|
|
48 |
query = sql.select(cls).where(cls.user_id == user_id)
|
49 |
transactions = await db.scalars(query)
|
50 |
return transactions
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
23 |
|
24 |
@classmethod
|
25 |
async def create(cls: "type[Transaction]", db: AsyncSession, **kwargs) -> "Transaction":
|
26 |
+
query = sql.insert(cls).values(**kwargs)
|
27 |
transactions = await db.execute(query)
|
28 |
transaction = transactions.first()
|
29 |
await db.commit()
|
|
|
48 |
query = sql.select(cls).where(cls.user_id == user_id)
|
49 |
transactions = await db.scalars(query)
|
50 |
return transactions
|
51 |
+
|
52 |
+
@classmethod
|
53 |
+
async def get_by_user_between_dates(
|
54 |
+
cls: "type[Transaction]", db: AsyncSession, user_id: int, start_date: datetime, end_date: datetime
|
55 |
+
) -> "List[Transaction]":
|
56 |
+
query = sql.select(cls).where(cls.user_id == user_id).where(cls.transaction_date.between(start_date, end_date))
|
57 |
+
transactions = await db.scalars(query)
|
58 |
+
return transactions
|
app/model/user.py
CHANGED
@@ -19,6 +19,7 @@ class User(Base, BaseModel):
|
|
19 |
is_deleted: Mapped[bool] = mapped_column(default=False)
|
20 |
|
21 |
transactions = relationship("Transaction", back_populates="user")
|
|
|
22 |
|
23 |
@classmethod
|
24 |
async def create(cls: "type[User]", db: AsyncSession, **kwargs: UserCreate) -> "User":
|
|
|
19 |
is_deleted: Mapped[bool] = mapped_column(default=False)
|
20 |
|
21 |
transactions = relationship("Transaction", back_populates="user")
|
22 |
+
income_statements = relationship("IncomeStatement", back_populates="user")
|
23 |
|
24 |
@classmethod
|
25 |
async def create(cls: "type[User]", db: AsyncSession, **kwargs: UserCreate) -> "User":
|
app/schema/index.py
CHANGED
@@ -51,3 +51,17 @@ class TransactionCreate(TransactionResponse):
|
|
51 |
|
52 |
class Transaction(TransactionResponse):
|
53 |
user: User
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
51 |
|
52 |
class Transaction(TransactionResponse):
|
53 |
user: User
|
54 |
+
|
55 |
+
|
56 |
+
class IncomeStatementCreate(PydanticBaseModel):
|
57 |
+
user_id: int
|
58 |
+
date_from: datetime
|
59 |
+
date_to: datetime
|
60 |
+
|
61 |
+
|
62 |
+
class IncomeStatementResponse(PydanticBaseModel):
|
63 |
+
id: int
|
64 |
+
date_from: datetime
|
65 |
+
date_to: datetime
|
66 |
+
income: dict
|
67 |
+
expenses: dict
|
app/service/income_statement.py
ADDED
@@ -0,0 +1,15 @@
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1 |
+
from app.schema.index import IncomeStatementCreate
|
2 |
+
from app.model.transaction import Transaction as TransactionModel
|
3 |
+
from app.model.income_statement import IncomeStatement as IncomeStatementModel
|
4 |
+
from sqlalchemy.ext.asyncio import AsyncSession
|
5 |
+
|
6 |
+
|
7 |
+
async def call_llm_to_create_income_statement(payload: IncomeStatementCreate, db: AsyncSession) -> None:
|
8 |
+
transactions = await TransactionModel.get_by_user_between_dates(
|
9 |
+
db, payload.user_id, payload.date_from, payload.date_to
|
10 |
+
)
|
11 |
+
|
12 |
+
# TODO: Call LLM to generate income and expenses
|
13 |
+
income = {}
|
14 |
+
expenses = {}
|
15 |
+
await IncomeStatementModel.create(db, **payload, income=income, expenses=expenses)
|
main.py
CHANGED
@@ -8,6 +8,7 @@ from fastapi.responses import RedirectResponse
|
|
8 |
from app.api.routers.chat import chat_router
|
9 |
from app.api.routers.user import user_router
|
10 |
from app.api.routers.transaction import transaction_router
|
|
|
11 |
from app.settings import init_settings
|
12 |
from fastapi.staticfiles import StaticFiles
|
13 |
from alembic.config import Config
|
@@ -53,16 +54,19 @@ def init_app(init_db: bool = True) -> FastAPI:
|
|
53 |
app.include_router(chat_router, prefix="/api/chat")
|
54 |
app.include_router(user_router)
|
55 |
app.include_router(transaction_router)
|
|
|
56 |
|
57 |
return app
|
58 |
|
59 |
|
60 |
app = init_app()
|
61 |
|
62 |
-
if __name__ == "__main__":
|
63 |
-
# Redirect to documentation page when accessing base URL
|
64 |
-
@app.get("/")
|
65 |
-
async def redirect_to_docs():
|
66 |
-
return RedirectResponse(url="/docs")
|
67 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
68 |
uvicorn.run(app="main:app", host=env.APP_HOST, port=env.APP_PORT, reload=(env.ENVIRONMENT == "dev"))
|
|
|
8 |
from app.api.routers.chat import chat_router
|
9 |
from app.api.routers.user import user_router
|
10 |
from app.api.routers.transaction import transaction_router
|
11 |
+
from app.api.routers.income_statement import income_statement_router
|
12 |
from app.settings import init_settings
|
13 |
from fastapi.staticfiles import StaticFiles
|
14 |
from alembic.config import Config
|
|
|
54 |
app.include_router(chat_router, prefix="/api/chat")
|
55 |
app.include_router(user_router)
|
56 |
app.include_router(transaction_router)
|
57 |
+
app.include_router(income_statement_router)
|
58 |
|
59 |
return app
|
60 |
|
61 |
|
62 |
app = init_app()
|
63 |
|
|
|
|
|
|
|
|
|
|
|
64 |
|
65 |
+
# Redirect to documentation page when accessing base URL
|
66 |
+
@app.get("/")
|
67 |
+
async def redirect_to_docs():
|
68 |
+
return RedirectResponse(url="/docs")
|
69 |
+
|
70 |
+
|
71 |
+
if __name__ == "__main__":
|
72 |
uvicorn.run(app="main:app", host=env.APP_HOST, port=env.APP_PORT, reload=(env.ENVIRONMENT == "dev"))
|
migration/env.py
CHANGED
@@ -2,9 +2,9 @@ import asyncio
|
|
2 |
from logging.config import fileConfig
|
3 |
|
4 |
from app.engine.postgresdb import Base
|
5 |
-
from sqlalchemy import
|
6 |
from sqlalchemy import pool
|
7 |
-
from sqlalchemy.ext.asyncio import
|
8 |
|
9 |
from config.index import config as env
|
10 |
|
@@ -22,8 +22,9 @@ if config.config_file_name is not None:
|
|
22 |
fileConfig(config.config_file_name)
|
23 |
|
24 |
# Import all models so they're registered with SQLAlchemy.
|
25 |
-
import app.model.transaction
|
26 |
import app.model.user
|
|
|
|
|
27 |
|
28 |
# add your model's MetaData object here
|
29 |
# for 'autogenerate' support
|
|
|
2 |
from logging.config import fileConfig
|
3 |
|
4 |
from app.engine.postgresdb import Base
|
5 |
+
from sqlalchemy import Connection
|
6 |
from sqlalchemy import pool
|
7 |
+
from sqlalchemy.ext.asyncio import async_engine_from_config
|
8 |
|
9 |
from config.index import config as env
|
10 |
|
|
|
22 |
fileConfig(config.config_file_name)
|
23 |
|
24 |
# Import all models so they're registered with SQLAlchemy.
|
|
|
25 |
import app.model.user
|
26 |
+
import app.model.transaction
|
27 |
+
import app.model.income_statement
|
28 |
|
29 |
# add your model's MetaData object here
|
30 |
# for 'autogenerate' support
|
migration/versions/4e76691ab103_add_income_statement_table.py
ADDED
@@ -0,0 +1,43 @@
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1 |
+
"""add_income_statement_table
|
2 |
+
|
3 |
+
Revision ID: 4e76691ab103
|
4 |
+
Revises: 7ea44cbc5b1f
|
5 |
+
Create Date: 2024-06-03 23:59:03.717778
|
6 |
+
|
7 |
+
"""
|
8 |
+
from typing import Sequence, Union
|
9 |
+
|
10 |
+
from alembic import op
|
11 |
+
import sqlalchemy as sa
|
12 |
+
from sqlalchemy.dialects import postgresql
|
13 |
+
|
14 |
+
# revision identifiers, used by Alembic.
|
15 |
+
revision: str = '4e76691ab103'
|
16 |
+
down_revision: Union[str, None] = '7ea44cbc5b1f'
|
17 |
+
branch_labels: Union[str, Sequence[str], None] = None
|
18 |
+
depends_on: Union[str, Sequence[str], None] = None
|
19 |
+
|
20 |
+
|
21 |
+
def upgrade() -> None:
|
22 |
+
# ### commands auto generated by Alembic - please adjust! ###
|
23 |
+
op.create_table('income_statement',
|
24 |
+
sa.Column('income', postgresql.JSON(astext_type=sa.Text()), nullable=True),
|
25 |
+
sa.Column('expenses', postgresql.JSON(astext_type=sa.Text()), nullable=True),
|
26 |
+
sa.Column('date_from', sa.DateTime(), nullable=False),
|
27 |
+
sa.Column('date_to', sa.DateTime(), nullable=False),
|
28 |
+
sa.Column('user_id', sa.Integer(), nullable=False),
|
29 |
+
sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
|
30 |
+
sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
|
31 |
+
sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
|
32 |
+
sa.ForeignKeyConstraint(['user_id'], ['users.id'], ),
|
33 |
+
sa.PrimaryKeyConstraint('id')
|
34 |
+
)
|
35 |
+
op.create_index(op.f('ix_income_statement_id'), 'income_statement', ['id'], unique=False)
|
36 |
+
# ### end Alembic commands ###
|
37 |
+
|
38 |
+
|
39 |
+
def downgrade() -> None:
|
40 |
+
# ### commands auto generated by Alembic - please adjust! ###
|
41 |
+
op.drop_index(op.f('ix_income_statement_id'), table_name='income_statement')
|
42 |
+
op.drop_table('income_statement')
|
43 |
+
# ### end Alembic commands ###
|