Untitled

mail@pastecode.io avatar
unknown
python
a year ago
1.6 kB
3
Indexable
Never
```python
from peewee import *

db = SqliteDatabase('your_database_name.db')  # Замените 'your_database_name.db' на имя вашей базы данных

class User(Model):
    id = IntegerField(primary_key=True)
    name = CharField()
    registered = DateTimeField()

    class Meta:
        database = db

class Transaction(Model):
    id = IntegerField(primary_key=True)
    owner = ForeignKeyField(User, backref='transactions')
    direction = CharField(choices=['out', 'in'])
    amount = IntegerField()
    created = DateTimeField()

    class Meta:
        database = db

# Получаем список пользователей с данными
users = User.select(User.id, User.name,
                    fn.SUM(Case(None, ((Transaction.direction == 'in', Transaction.amount),), 0)).alias('incoming_transactions_sum'),
                    fn.SUM(Case(None, ((Transaction.direction == 'out', Transaction.amount),), 0)).alias('outgoing_transactions_sum'),
                    fn.SUM(Case(None, ((Transaction.direction == 'in', Transaction.amount), (Transaction.direction == 'out', -Transaction.amount)), 0)).alias('balance'))
            .join(Transaction)
            .where(Transaction.created.between('2023-07-05', '2023-07-20'))
            .group_by(User.id, User.name)
            .order_by(User.id)

# Выводим результат
for user in users:
    print(f"ID: {user.id}, Name: {user.name}, Incoming Transactions Sum: {user.incoming_transactions_sum}, Outgoing Transactions Sum: {user.outgoing_transactions_sum}, Balance: {user.balance}")