async def get_accounts(owner: Dict, conn: SAConnection) -> Dict:
accounts = {}
params = table.c.owner_id == owner.get('id')
async for row in conn.execute(sqlalchemy.select([table]).where(params)):
account = dict(zip(row.keys(), row.values()))
accounts[account['id']] = account
total = await conn.scalar(
sqlalchemy.select([sqlalchemy.func.count()]).select_from(
table).where(params)
)
balance = []
query = sqlalchemy.select([balance_table]).where(
balance_table.c.account_id.in_(accounts.keys())).order_by(
balance_table.c.date.desc())
async for row in conn.execute(query):
item = dict(zip(row.keys(), row.values()))
balance.append(item)
for key, group in groupby(balance, lambda b: b.get('account_id')):
accounts[key]['balance'] = list(group)
return accounts, total
async def fetch_many(cls, conn: SAConnection, cte: CTE = None):
results = OrderedDict()
if cte is None:
cte = cls.Options.db_table
query = (
cte
.outerjoin(tbl.books_stores)
.outerjoin(tbl.books,
tbl.books_stores.c.book_id == tbl.books.c.id)
.select(use_labels=True)
)
books = {}
async for row in conn.execute(query):
store_id = row[cte.c.id]
store = results.get(store_id)
if store is None:
store = Store.from_row(row, books=[], alias=cte)
results[store_id] = store
book_id = row[tbl.books.c.id]
if book_id:
book = books.get(book_id, Book.not_populated(book_id))
if book not in store.books:
store.books.append(book)
return results
async def fetch_many(cls, conn: SAConnection, cte: CTE = None):
results = OrderedDict()
imageable_map = {
'authors': Author,
'books': Book,
'series': Series
}
if cte is None:
cte = cls.Options.db_table
query = cte.select()
async for row in conn.execute(query):
photo_id = row[cte.c.id]
photo = results.get(photo_id)
if photo is None:
imageable_id = row[cte.c.imageable_id]
imageable_type = row[cte.c.imageable_type]
imageable_model = imageable_map[imageable_type]
imageable = imageable_model.not_populated(imageable_id)
photo = Photo.from_row(row, imageable=imageable, alias=cte)
results[photo_id] = photo
return results
async def update_balance(balance: List, account: Dict, conn: SAConnection):
existed = {}
query = sqlalchemy.select([table]).where(
table.c.account_id == account.get('id')).order_by(table.c.date.asc())
async for item in conn.execute(query):
key = datetime.combine(item.date, datetime.min.time())
existed[key] = {
'id': item.id,
'income': item.income,
'expense': item.expense,
'remain': item.remain,
'date': item.date
}
for item in balance:
if item['date'] in existed:
query = sqlalchemy.update(table).where(
table.c.id == existed[item['date']]['id']).values(**item)
else:
query = sqlalchemy.insert(table, values={
'account_id': account['id'], **item
})
await conn.execute(query)
async def fetch_many(cls, conn: SAConnection, cte: CTE = None):
results = OrderedDict()
if cte is None:
cte = cls.Options.db_table
query = (
cte
.join(tbl.authors)
.outerjoin(tbl.series)
.outerjoin(tbl.chapters)
.outerjoin(tbl.photos,
sa.and_(
tbl.photos.c.imageable_id == cte.c.id,
tbl.photos.c.imageable_type == 'books'
))
.select(use_labels=True)
)
photos = {}
chapters = {}
async for row in conn.execute(query):
book_id = row[cte.c.id]
book = results.get(book_id)
if book is None:
book_author = Author.not_populated(row[tbl.authors.c.id])
book_series_id = row[tbl.series.c.id]
book_series = Series.not_populated(book_series_id) \
if book_series_id \
else None
book = Book.from_row(row, author=book_author, chapters=[],
series=book_series,
photos=[], alias=cte)
results[book_id] = book
photo_id = row[tbl.photos.c.id]
if photo_id:
photo = photos.get(photo_id, Photo.not_populated(photo_id))
if photo not in book.photos:
book.photos.append(photo)
chapter_id = row[tbl.chapters.c.id]
if chapter_id:
chapter = chapters.get(chapter_id,
Chapter.not_populated(chapter_id))
if chapter not in book.chapters:
book.chapters.append(chapter)
return results
async def calculate_balance(account: Dict, until: datetime,
conn: SAConnection) -> List:
query = '''
SELECT
SUM(transactions.amount),
transactions.type,
to_char(transactions.created_on, 'MM-YYYY') as date
FROM transactions
WHERE (
transactions.account_id = {account_id}
)
GROUP BY transactions.type, transactions.created_on
ORDER BY transactions.created_on ASC;
'''.format(account_id=account.get('id'))
total_by_month = {}
min_date = until
async for item in conn.execute(query):
date = datetime.strptime(item.date, '%m-%Y')
min_date = min(date, min_date)
if item.date in total_by_month:
total_by_month[item.date][item.type] = item.sum
else:
total_by_month[item.date] = {item.type: item.sum}
if not total_by_month:
t = min_date
date = datetime(year=t.year, month=t.month, day=1)
total_by_month[date.strftime('%m-%Y')] = {'income': 0, 'expense': 0}
balance = []
remain = account.get('original_amount', 0)
if not isinstance(remain, Decimal):
remain = Decimal(remain)
for d in rrule(MONTHLY, dtstart=min_date, until=until): # type: datetime
key = d.strftime('%m-%Y')
expense = Decimal(0)
income = Decimal(0)
if key in total_by_month:
expense = total_by_month[key].get('expense', Decimal(0))
income = total_by_month[key].get('income', Decimal(0))
balance.append({
'expense': expense, 'income': income, 'remain': remain, 'date': d
})
remain = remain + income - expense
return balance
async def fetch_many(cls, conn: SAConnection, cte: CTE = None):
results = OrderedDict()
if cte is None:
cte = cls.Options.db_table
query = (
cte
.outerjoin(tbl.books,
tbl.books.c.author_id == cte.c.id)
.outerjoin(tbl.photos,
sa.and_(
tbl.photos.c.imageable_id == cte.c.id,
tbl.photos.c.imageable_type == 'authors'
))
.select(use_labels=True)
)
books = {}
photos = {}
async for row in conn.execute(query):
author_id = row[cte.c.id]
author = results.get(author_id)
if author is None:
author = Author.from_row(row, books=[], photos=[], alias=cte)
results[author_id] = author
book_id = row[tbl.books.c.id]
if book_id:
book = books.get(book_id, Book.not_populated(book_id))
if book not in author.books:
author.books.append(book)
photo_id = row[tbl.photos.c.id]
if photo_id:
photo = photos.get(photo_id, Photo.not_populated(photo_id))
if photo not in author.photos:
author.photos.append(photo)
return results
async def fetch_many(cls, conn: SAConnection, cte: CTE = None):
results = OrderedDict()
if cte is None:
cte = cls.Options.db_table
query = cte.select()
async for row in conn.execute(query):
chapter_id = row[cte.c.id]
chapter = results.get(chapter_id)
if chapter is None:
chapter = \
cls.from_row(row,
book=Book.not_populated(row[cte.c.book_id]),
alias=cte)
results[chapter_id] = chapter
return results
async def get_image_colors(connection: SAConnection,
image_id: int) -> List[Color]:
"""
Retrieve a list of image colors from DB.
"""
sql = sa.text("""
SELECT * FROM image_color WHERE image_id = :image_id;
""")
colors: List[Color] = []
async for row in connection.execute(sql, {"image_id": image_id}):
color = Color(
L=row["L"],
a=row["a"],
b=row["b"],
percentage=row["percentage"],
name=row["name"],
name_distance=row["name_distance"],
)
colors.append(color)
return colors
