def balance_sheet(currency, period):
periods = db.session \
.query(\
func.date_part('year', models.LedgerEntries.date), \
func.date_part('month', models.LedgerEntries.date)) \
.group_by( \
func.date_part('year', models.LedgerEntries.date), \
func.date_part('month', models.LedgerEntries.date)) \
.all()
periods = sorted([date(int(period[0]), int(period[1]), 1) for period in periods])
if period == 'Current':
period = datetime.now()
else:
period = datetime.strptime(period, "%Y-%m")
lastday = calendar.monthrange(period.year, period.month)[1]
year = period.year
month = period.month
period = datetime(year, month, lastday, 23, 59, 59)
period_beg = datetime(period.year, period.month, 1, 0, 0, 0, 0)
period_end = datetime(period.year, period.month, lastday, 23, 59, 59, 999999)
elements = db.session \
.query(models.Elements) \
.join(models.Classifications) \
.join(models.Accounts) \
.join(models.Subaccounts) \
.all()
retained_earnings = 0
for element in elements:
element.balance = 0
for classification in element.classifications:
classification.balance = 0
for account in classification.accounts:
account.balance = 0
for subaccount in account.subaccounts:
subaccount.balance = 0
subaccount.ledgerentries = [c for c in subaccount.ledgerentries if c.date <= period_end ]
for entry in subaccount.ledgerentries:
if entry.currency == currency:
element.balance -= entry.credit
classification.balance -= entry.credit
account.balance -= entry.credit
subaccount.balance -= entry.credit
element.balance += entry.debit
classification.balance += entry.debit
account.balance += entry.debit
subaccount.balance += entry.debit
if element.name == 'Equity':
retained_earnings = -element.balance
elements = [c for c in elements if c.name in ['Assets', 'Liabilities']]
return render_template('balance_sheet.html',
periods=periods,
currency=currency,
elements=elements,
retained_earnings=retained_earnings,
period=period_end)
class Offender(db.Model):
__tablename__ = 'offenders'
id = db.Column(db.Integer, primary_key=True)
tdcj_id = db.Column(db.Integer)
last_name = db.Column(db.String(50))
first_name = db.Column(db.String(50))
age = db.Column(db.Integer)
dob = db.Column(db.Date)
execution_num = db.Column(db.Integer)
execution_date = db.Column(db.Date)
execution_year = func.date_part('year', execution_date)
execution_month = func.date_part('month', execution_date)
execution_day = db.Column(db.Integer)
offense_date = db.Column(db.Date)
offense_county = db.Column(db.String(50))
received_date = db.Column(db.Date)
gender = db.Column(db.String(1))
race = db.Column(db.String(20))
last_statement = db.Column(db.Text)
teaser = db.Column(db.Text)
info_url = db.Column(db.String(200))
statement_url = db.Column(db.String(200))
def __init__(self,
id=None,
tdcj_id=None,
last_name=None,
first_name=None,
age=None,
dob=None,
execution_num=None,
execution_date=None,
offense_date=None,
offense_county=None,
received_date=None,
gender=None,
race=None,
last_statement=None,
info_url=None,
statement_url=None):
self.tdcj_id = tdcj_id
self.last_name = last_name
self.first_name = first_name
self.age = age
self.dob = dob
self.execution_num = execution_num
self.execution_date = execution_date
self.execution_day = doy_leap(execution_date)
self.offense_date = offense_date
self.offense_county = offense_county
self.received_date = received_date
self.gender = gender
self.race = race
self.last_statement = last_statement
self.info_url = info_url
self.statement_url = statement_url
def balance_sheet_historical(currency, period):
periods = db.session \
.query(\
func.date_part('year', models.LedgerEntries.date), \
func.date_part('month', models.LedgerEntries.date)) \
.group_by( \
func.date_part('year', models.LedgerEntries.date), \
func.date_part('month', models.LedgerEntries.date)) \
.all()
periods = sorted([date(int(period[0]), int(period[1]), 1) for period in periods])
period = datetime.strptime(period, "%Y-%m")
lastday = calendar.monthrange(period.year, period.month)[1]
period_beg = datetime(period.year, period.month, 1, 0, 0, 0, 0)
period_end = datetime(period.year, period.month, lastday, 23, 59, 59, 999999)
elements = db.session \
.query(models.Elements) \
.join(models.Classifications) \
.join(models.Accounts) \
.join(models.Subaccounts) \
.all()
retained_earnings = 0
for element in elements:
element.balance = 0
for classification in element.classifications:
classification.balance = 0
for account in classification.accounts:
account.balance = 0
for subaccount in account.subaccounts:
subaccount.balance = 0
subaccount.ledgerentries = [c for c in subaccount.ledgerentries if c.date <= period_end ]
for ledger_entry in subaccount.ledgerentries:
if ledger_entry.currency == currency:
if ledger_entry.tside == 'credit':
element.balance -= ledger_entry.amount
classification.balance -= ledger_entry.amount
account.balance -= ledger_entry.amount
subaccount.balance -= ledger_entry.amount
elif ledger_entry.tside == 'debit':
element.balance += ledger_entry.amount
classification.balance += ledger_entry.amount
account.balance += ledger_entry.amount
subaccount.balance += ledger_entry.amount
if element.name == 'Equity':
retained_earnings = -element.balance
print(retained_earnings)
elements = [c for c in elements if c.name in ['Assets', 'Liabilities']]
return render_template('financial_statements/balance_sheet.html',
periods=periods,
currency=currency,
elements=elements,
retained_earnings=retained_earnings,
period=period_end)
def balance_sheet(currency):
periods = db.session \
.query(\
func.date_part('year', models.LedgerEntries.date), \
func.date_part('month', models.LedgerEntries.date)) \
.group_by( \
func.date_part('year', models.LedgerEntries.date), \
func.date_part('month', models.LedgerEntries.date)) \
.all()
periods = sorted([date(int(period[0]), int(period[1]), 1) for period in periods])
period = datetime.now()
period_beg = datetime(period.year, period.month, 1, 0, 0, 0, 0)
period_end = datetime(period.year, period.month, period.day, 23, 59, 59, 999999)
elements = db.session \
.query(models.Elements) \
.join(models.Classifications) \
.join(models.Accounts) \
.join(models.Subaccounts) \
.all()
retained_earnings = 0
for element in elements:
element.balance = 0
for classification in element.classifications:
classification.balance = 0
for account in classification.accounts:
account.balance = 0
for subaccount in account.subaccounts:
subaccount.balance = 0
subaccount.ledgerentries = [c for c in subaccount.ledgerentries if c.date <= period_end ]
for ledger_entry in subaccount.ledgerentries:
if ledger_entry.currency == currency:
if ledger_entry.tside == 'credit':
element.balance -= ledger_entry.amount
classification.balance -= ledger_entry.amount
account.balance -= ledger_entry.amount
subaccount.balance -= ledger_entry.amount
elif ledger_entry.tside == 'debit':
element.balance += ledger_entry.amount
classification.balance += ledger_entry.amount
account.balance += ledger_entry.amount
subaccount.balance += ledger_entry.amount
if element.name == 'Equity':
retained_earnings = -element.balance
print(retained_earnings)
elements = [c for c in elements if c.name in ['Assets', 'Liabilities']]
return render_template('financial_statements/balance_sheet.html',
periods=periods,
currency=currency,
elements=elements,
retained_earnings=retained_earnings,
period=period_end)
def get_years(model):
if model == "distinct":
q = DB.session.query(
label("year", distinct(func.date_part("year", VSynthese.date_min)))
).order_by("year")
if model == "min-max":
q = DB.session.query(
func.min(func.date_part("year", VSynthese.date_min)),
func.max(func.date_part("year", VSynthese.date_min)),
)
return q.all()
def statement_of_cash_flows(currency, period):
periods = db.session \
.query(\
func.date_part('year', models.LedgerEntries.date), \
func.date_part('month', models.LedgerEntries.date)) \
.group_by( \
func.date_part('year', models.LedgerEntries.date), \
func.date_part('month', models.LedgerEntries.date)) \
.all()
periods = sorted([date(int(period[0]), int(period[1]), 1) for period in periods])
if period == 'Current':
period = datetime.now()
lastday = period.day
else:
period = datetime.strptime(period, "%Y-%m")
lastday = calendar.monthrange(period.year, period.month)[1]
period_beg = datetime(period.year, period.month, 1, 0, 0, 0, 0)
period_end = datetime(period.year, period.month, lastday, 23, 59, 59, 999999)
elements = db.session \
.query(models.Elements) \
.join(models.Classifications) \
.filter(models.Classifications.name.in_(['Revenues', 'Expenses', 'Gains', 'Losses']))\
.join(models.Accounts) \
.join(models.Subaccounts) \
.all()
net_income = 0
for element in elements:
element.classifications = [c for c in element.classifications if c.name in ['Revenues', 'Expenses', 'Gains', 'Losses']]
for classification in element.classifications:
classification.balance = 0
for account in classification.accounts:
account.balance = 0
for subaccount in account.subaccounts:
subaccount.balance = 0
subaccount.ledgerentries = [c for c in subaccount.ledgerentries if period_beg <= c.date <= period_end ]
for ledger_entry in subaccount.ledgerentries:
if ledger_entry.currency == currency:
if ledger_entry.tside == 'credit':
classification.balance -= ledger_entry.amount
account.balance -= ledger_entry.amount
subaccount.balance -= ledger_entry.amount
elif ledger_entry.tside == 'debit':
classification.balance += ledger_entry.amount
account.balance += ledger_entry.amount
subaccount.balance += ledger_entry.amount
return render_template('financial_statements/statement_of_cash_flows.html',
period = period,
periods = periods,
currency = currency,
elements = elements,
net_income = net_income)
def income_statement(currency, period):
periods = db.session \
.query(\
func.date_part('year', models.LedgerEntries.date), \
func.date_part('month', models.LedgerEntries.date)) \
.group_by( \
func.date_part('year', models.LedgerEntries.date), \
func.date_part('month', models.LedgerEntries.date)) \
.all()
periods = sorted([date(int(period[0]), int(period[1]), 1) for period in periods])
if period == 'Current':
period = datetime.now()
else:
period = datetime.strptime(period, "%Y-%m")
lastday = calendar.monthrange(period.year, period.month)[1]
year = period.year
month = period.month
period = datetime(year, month, lastday, 23, 59, 59)
period_beg = datetime(period.year, period.month, 1, 0, 0, 0, 0)
period_end = datetime(period.year, period.month, lastday, 23, 59, 59, 999999)
elements = db.session \
.query(models.Elements) \
.join(models.Classifications) \
.filter(models.Classifications.name.in_(['Revenues', 'Expenses', 'Gains', 'Losses']))\
.join(models.Accounts) \
.join(models.Subaccounts) \
.all()
net_income = 0
for element in elements:
element.classifications = [c for c in element.classifications if c.name in ['Revenues', 'Expenses', 'Gains', 'Losses']]
for classification in element.classifications:
for account in classification.accounts:
for subaccount in account.subaccounts:
subaccount.total = 0
subaccount.ledgerentries = [c for c in subaccount.ledgerentries if period_beg <= c.date <= period_end ]
for ledger_entry in subaccount.ledgerentries:
if ledger_entry.currency == currency:
net_income += ledger_entry.credit
subaccount.total += ledger_entry.credit
net_income -= ledger_entry.debit
subaccount.total -= ledger_entry.debit
return render_template('income_statement.html',
title = 'Income Statement',
periods = periods,
currency = currency,
elements = elements,
net_income = net_income,
period=period)
def get_synthese_stat():
params = request.args
q = DB.session.query(
label("year", func.date_part("year", VSynthese.date_min)),
func.count(VSynthese.id_synthese),
func.count(distinct(VSynthese.cd_ref)),
).group_by("year")
if ("selectedRegne" in params) and (params["selectedRegne"] != ""):
q = q.filter(VSynthese.regne == params["selectedRegne"])
if ("selectedPhylum" in params) and (params["selectedPhylum"] != ""):
q = q.filter(VSynthese.phylum == params["selectedPhylum"])
if "selectedClasse" in params and (params["selectedClasse"] != ""):
q = q.filter(VSynthese.classe == params["selectedClasse"])
if "selectedOrdre" in params and (params["selectedOrdre"] != ""):
q = q.filter(VSynthese.ordre == params["selectedOrdre"])
if "selectedFamille" in params and (params["selectedFamille"] != ""):
q = q.filter(VSynthese.famille == params["selectedFamille"])
if ("selectedGroup2INPN"
in params) and (params["selectedGroup2INPN"] != ""):
q = q.filter(VSynthese.group2_inpn == params["selectedGroup2INPN"])
if ("selectedGroup1INPN"
in params) and (params["selectedGroup1INPN"] != ""):
q = q.filter(VSynthese.group1_inpn == params["selectedGroup1INPN"])
if ("taxon" in params) and (params["taxon"] != ""):
q = q.filter(VSynthese.cd_ref == params["taxon"])
return q.all()
def selectReportMonthDetails(userId, year, month, startDay, endDay):
"""選択された月の日別とその稼働時間を取得するDB処理
:param userId: 登録ユーザID
:param year: 登録年
:param month: 登録月
:param startDay: 月の初日
:param endDay: 月の最後の日
"""
subq1 = db.session.query(
func.generate_series(
func.date(startDay) - func.CURRENT_DATE(),
func.date(endDay) - func.CURRENT_DATE()).label('i')).subquery()
subq2 = db.session.query(
func.cast(func.date_part('day',
func.CURRENT_DATE() + subq1.c.i),
Integer).label('day')).subquery()
monthDetails = db.session.query(
subq2.c.day,
__model.rest_flg,
db.func.to_char(
__model.normal_working_hours + __model.overtime_hours +
__model.holiday_work_hours, '999D99').label('workTime'),
).outerjoin(
__model,
and_(subq2.c.day == __model.work_day, __model.user_id == userId,
__model.work_year == year,
__model.work_month == month)).order_by(subq2.c.day).all()
return monthDetails
def income_statement(currency):
periods = db.session \
.query(\
func.date_part('year', models.LedgerEntries.date),\
func.date_part('month', models.LedgerEntries.date)) \
.group_by( \
func.date_part('year', models.LedgerEntries.date),\
func.date_part('month', models.LedgerEntries.date)) \
.all()
periods = sorted([date(int(period[0]), int(period[1]), 1) for period in periods])
period = datetime.now()
period_beg = datetime(period.year, period.month, 1, 0, 0, 0, 0)
period_end = datetime(period.year, period.month, period.day, 23, 59, 59, 999999)
elements = db.session \
.query(models.Elements) \
.join(models.Classifications) \
.filter(models.Classifications.name.in_(['Revenues', 'Expenses', 'Gains', 'Losses']))\
.join(models.Accounts) \
.join(models.Subaccounts) \
.all()
net_income = 0
for element in elements:
element.classifications = [c for c in element.classifications if c.name in ['Revenues', 'Expenses', 'Gains', 'Losses']]
for classification in element.classifications:
for account in classification.accounts:
for subaccount in account.subaccounts:
subaccount.total = 0
subaccount.ledgerentries = [c for c in subaccount.ledgerentries if period_beg <= c.date <= period_end ]
for ledger_entry in subaccount.ledgerentries:
if ledger_entry.currency == currency:
if ledger_entry.tside == 'credit':
subaccount.total += ledger_entry.amount
net_income += ledger_entry.amount
elif ledger_entry.tside == 'debit':
net_income -= ledger_entry.amount
subaccount.total -= ledger_entry.amount
return render_template('financial_statements/income_statement.html',
title = 'Income Statement',
periods = periods,
currency = currency,
elements = elements,
net_income = net_income)
def ledger_page(accountName, currency, groupby, interval):
if groupby == "Daily":
interval = datetime.strptime(interval, "%m-%d-%Y")
year = interval.year
month = interval.month
day = interval.day
ledger_entries = models.LedgerEntries \
.query \
.filter_by(ledger=accountName) \
.filter_by(currency=currency) \
.filter( \
func.date_part('year', models.LedgerEntries.date)==year, \
func.date_part('month', models.LedgerEntries.date)==month, \
func.date_part('day', models.LedgerEntries.date)==day) \
.order_by(models.LedgerEntries.date) \
.order_by(models.LedgerEntries.tside.asc()) \
.all()
account = ledgers.foot_account(accountName, ledger_entries, 'All')
if groupby == "Monthly":
interval = datetime.strptime(interval, "%m-%Y")
year = interval.year
month = interval.month
ledger_entries = models.LedgerEntries\
.query\
.filter_by(ledger=accountName) \
.filter_by(currency=currency) \
.filter( \
func.date_part('year', models.LedgerEntries.date)==year, \
func.date_part('month', models.LedgerEntries.date)==month)\
.order_by(models.LedgerEntries.date) \
.order_by(models.LedgerEntries.tside.desc()) \
.all()
account = ledgers.foot_account(accountName, ledger_entries, 'All')
return render_template('bookkeeping/ledger.html',
title = 'Ledger',
account=account,
ledger_entries=ledger_entries,
groupby2 = groupby,
groupby = 'All',
accountName=accountName,
interval=interval,
currency=currency)
def posts_per_hour(self, start_date=None, end_date=None):
q = select([func.date_part('hour', t_posts.c.created_at),
func.count(t_posts.c.id)],
t_posts.c.user_id==self.id).group_by('1').order_by('1')
if start_date: q = q.where(t_posts.c.created_at>=start_date)
if end_date: q = q.where(t_posts.c.created_at<end_date)
data = dict(meta.Session.execute(q).fetchall())
hours = dict([(hour, 0) for hour in range(0, 24)])
return dict(hours, **data).values()
def posts_per_dow(self, start_date=None, end_date=None):
q = select([func.date_part('isodow', t_posts.c.created_at),
func.count(t_posts.c.id)],
t_posts.c.user_id==self.id).group_by('1').order_by('1')
if start_date: q = q.where(t_posts.c.created_at>=start_date)
if end_date: q = q.where(t_posts.c.created_at<end_date)
data = dict(meta.Session.execute(q).fetchall())
days = dict([(day, 0) for day in range(1, 8)])
return dict(days, **data).values()
def posts_per_hour(self, start_date=None, end_date=None):
q = select([
func.date_part('hour', t_posts.c.created_at),
func.count(t_posts.c.id)
], t_posts.c.user_id == self.id).group_by('1').order_by('1')
if start_date: q = q.where(t_posts.c.created_at >= start_date)
if end_date: q = q.where(t_posts.c.created_at < end_date)
data = dict(meta.Session.execute(q).fetchall())
hours = dict([(hour, 0) for hour in range(0, 24)])
return dict(hours, **data).values()
def posts_per_dow(self, start_date=None, end_date=None):
q = select([
func.date_part('isodow', t_posts.c.created_at),
func.count(t_posts.c.id)
], t_posts.c.user_id == self.id).group_by('1').order_by('1')
if start_date: q = q.where(t_posts.c.created_at >= start_date)
if end_date: q = q.where(t_posts.c.created_at < end_date)
data = dict(meta.Session.execute(q).fetchall())
days = dict([(day, 0) for day in range(1, 8)])
return dict(days, **data).values()
def get_synthese_per_tax_level_stat(taxLevel):
params = request.args
if taxLevel == "Règne":
q = (DB.session.query(
func.coalesce(VSynthese.regne, "Not defined"),
func.count(VSynthese.id_synthese),
).group_by(VSynthese.regne).order_by(VSynthese.regne))
if taxLevel == "Phylum":
q = (DB.session.query(
func.coalesce(VSynthese.phylum, "Not defined"),
func.count(VSynthese.id_synthese),
).group_by(VSynthese.phylum).order_by(VSynthese.phylum))
if taxLevel == "Classe":
q = (DB.session.query(
func.coalesce(VSynthese.classe, "Not defined"),
func.count(VSynthese.id_synthese),
).group_by(VSynthese.classe).order_by(VSynthese.classe))
if taxLevel == "Ordre":
q = (DB.session.query(
func.coalesce(VSynthese.ordre, "Not defined"),
func.count(VSynthese.id_synthese),
).group_by(VSynthese.ordre).order_by(VSynthese.ordre))
if taxLevel == "Groupe INPN 1":
q = (DB.session.query(
func.coalesce(VSynthese.group1_inpn, "Not defined"),
func.count(VSynthese.id_synthese),
).group_by(VSynthese.group1_inpn).order_by(VSynthese.group1_inpn))
if taxLevel == "Groupe INPN 2":
q = (DB.session.query(
func.coalesce(VSynthese.group2_inpn, "Not defined"),
func.count(VSynthese.id_synthese),
).group_by(VSynthese.group2_inpn).order_by(VSynthese.group2_inpn))
if "selectedYearRange" in params:
yearRange = params["selectedYearRange"].split(",")
q = q.filter(
func.date_part("year", VSynthese.date_min) <= yearRange[1])
q = q.filter(
func.date_part("year", VSynthese.date_max) >= yearRange[0])
return q.all()
def trial_balance_historical(currency, groupby, period):
accountsQuery = db.session \
.query(models.LedgerEntries.ledger) \
.group_by(models.LedgerEntries.ledger) \
.filter(models.LedgerEntries.currency==currency) \
.all()
periods = db.session \
.query(\
func.date_part('year', models.LedgerEntries.date) + '-'+
func.date_part('month', models.LedgerEntries.date)) \
.group_by(\
func.date_part('year', models.LedgerEntries.date),\
func.date_part('month', models.LedgerEntries.date)) \
.filter(models.LedgerEntries.currency==currency) \
.all()
period = datetime.strptime(period, "%Y-%m")
year = period.year
month = period.month
day = calendar.monthrange(year, month)[1]
period = datetime(year, month, day, 23, 59, 59)
accounts = []
totalDebits = 0
totalCredits = 0
for accountResult in accountsQuery:
accountName = accountResult[0]
ledger_entries = models.LedgerEntries \
.query \
.filter_by(ledger=accountName) \
.filter_by(currency=currency) \
.filter( \
func.date_part('year', models.LedgerEntries.date)==year, \
func.date_part('month', models.LedgerEntries.date)==month) \
.order_by(models.LedgerEntries.date) \
.order_by(models.LedgerEntries.tside.desc()) \
.all()
query = ledgers.foot_account(accountName, ledger_entries, 'All')
totalDebits += query['debitBalance']
totalCredits += query['creditBalance']
accounts.append(query)
return render_template('bookkeeping/trial_balance.html',
currency=currency,
periods=periods,
period=period,
accounts=accounts,
totalDebits=totalDebits,
totalCredits=totalCredits)
To access built-in functions provided by the database we use func object.
The following listing shows how to use date/time, mathematical and string
functions found in PostgreSQL database.
"""
from sqlalchemy.sql import func
c = [
## date/time functions ##
func.timeofday(),
func.localtime(),
func.current_timestamp(),
func.date_part("month", func.now()),
func.now(),
## mathematical functions ##
func.pow(4,2),
func.sqrt(441),
func.pi(),
func.floor(func.pi()),
func.ceil(func.pi()),
## string functions ##
func.lower("ABC"),
func.upper("abc"),
func.length("abc"),
continue
logger.debug("len(required):".format(len(required)))
# add data to table
if len(required) > 1:
logger.debug("ADD TO HOURLY TABLE")
rows_added += add_data_hourly(required)
else:
logger.debug("TOO LESS DATA, cant add")
break
logger.info("{}.{}.{} rows_added: {}".format(g.name, d.name, t.name,
rows_added))
return rows_added
m = func.date_part('minute', cast(Data.stime, types.DateTime)).label('m')
h = func.date_part('hour', cast(Data.stime, types.DateTime)).label('h')
# Here define gateway, device and tags to add hourly procedure
gw_list = session.query(Gateway).all()
device_list = session.query(Device).all()
tag_list = session.query(Tag).filter(
Tag.name.ilike('%import%') & ~Tag.name.ilike('%part%')).all()
if __name__ == '__main__':
logger.info("Start process...")
for i, t in enumerate(tag_list):
logger.debug("Tags count:{} {}".format(i + 1, t.name))
rows_count = 0
for g in gw_list:
# on the 'X' axis
criteria_mapping = {
'second' : ('minute', 'Minute'),
'minute' : ('hour', 'Hour'),
'hour' : ('day', 'Day'),
'dow' : ('week', 'Week'),
'day' : ('month', 'Month'),
'week' : ('year', 'Year'),
'month' : ('year', 'Year'),
}
rebuilded_select = []
# TODO : Sûrement améliorable :
for index, statement in enumerate(self.select):
first_base_column = list(statement.base_columns)[0]
if hasattr(first_base_column, 'name') and first_base_column.name == 'date_trunc':
statement = func.date_part(self.granularity, statement).label(self.granularity)
extract_criteria = criteria_mapping.get(self.granularity, ('year', u'Année'))
self.group_by.append(extract(extract_criteria[0], self.date_criteria_field))
rebuilded_select.append(statement)
rebuilded_select.append(cast(extract(extract_criteria[0], self.date_criteria_field), Integer()).label(extract_criteria[1]))
else:
rebuilded_select.append(statement)
self.select = rebuilded_select
# Here is what we have generated so far
self.logger.debug("SELECT %s JOIN %s FILTERS %s %s %s GROUP BY %s",self.select,self.joins,self.start_filter, self.end_filter, self.filters,self.group_by)
# Alias identically named columns
already_seen_names = []
new_select = []
for column in self.select:
if column.key in already_seen_names:
new_select.append(column.label('%s_%s' % (column.parententity.mapped_table.name, column.key)))
def query_entries(accountName, groupby, currency):
if groupby == "All":
ledger_entries = models.LedgerEntries \
.query \
.filter_by(ledger=accountName) \
.filter_by(currency=currency) \
.order_by(models.LedgerEntries.date.desc()) \
.order_by(models.LedgerEntries.tside.desc()) \
.all()
account = foot_account(accountName, ledger_entries, 'All')
elif groupby == "Daily":
debit_ledger_entries = db.session\
.query( \
func.date_part('year', models.LedgerEntries.date), \
func.date_part('month', models.LedgerEntries.date), \
func.date_part('day', models.LedgerEntries.date), \
func.sum(models.LedgerEntries.amount)) \
.filter_by(ledger=accountName)\
.filter_by(tside='debit')\
.filter_by(currency=currency)\
.group_by( \
func.date_part('year', models.LedgerEntries.date), \
func.date_part('month', models.LedgerEntries.date), \
func.date_part('day', models.LedgerEntries.date)) \
.all()
credit_ledger_entries = db.session \
.query( \
func.date_part('year', models.LedgerEntries.date), \
func.date_part('month', models.LedgerEntries.date), \
func.date_part('day', models.LedgerEntries.date), \
func.sum(models.LedgerEntries.amount))\
.filter_by(currency=currency) \
.filter_by(ledger=accountName) \
.filter_by(tside='credit') \
.group_by( \
func.date_part('year', models.LedgerEntries.date), \
func.date_part('month', models.LedgerEntries.date), \
func.date_part('day', models.LedgerEntries.date)) \
.all()
ledger_entries = {}
for entry in debit_ledger_entries:
day = datetime.date(int(entry[0]), int(entry[1]), int(entry[2]))
if not day in ledger_entries:
ledger_entries[day] = {}
ledger_entries[day]['debit'] = int(entry[3])
for entry in credit_ledger_entries:
day = datetime.date(int(entry[0]), int(entry[1]), int(entry[2]))
if not day in ledger_entries:
ledger_entries[day] = {}
ledger_entries[day]['credit'] = int(entry[3])
ledger_entries = OrderedDict(sorted(ledger_entries.items()))
account = foot_account(accountName, ledger_entries, 'Summary')
elif groupby == "Monthly":
debit_ledger_entries = db.session \
.query( \
func.date_part('year', models.LedgerEntries.date), \
func.date_part('month', models.LedgerEntries.date), \
func.sum(models.LedgerEntries.amount)) \
.filter_by(ledger=accountName) \
.filter_by(currency=currency) \
.filter_by(tside='debit') \
.group_by( \
func.date_part('year', models.LedgerEntries.date), \
func.date_part('month', models.LedgerEntries.date)) \
.all()
credit_ledger_entries = db.session\
.query( \
func.date_part('year', models.LedgerEntries.date), \
func.date_part('month', models.LedgerEntries.date), \
func.sum(models.LedgerEntries.amount)) \
.filter_by(ledger=accountName) \
.filter_by(currency=currency) \
.filter_by(tside='credit') \
.group_by( \
func.date_part('year', models.LedgerEntries.date), \
func.date_part('month', models.LedgerEntries.date)) \
.all()
ledger_entries = {}
for entry in debit_ledger_entries:
month = datetime.date(int(entry[0]), int(entry[1]), 1)
if not month in ledger_entries:
ledger_entries[month] = {}
ledger_entries[month]['debit'] = int(entry[2])
for entry in credit_ledger_entries:
month = datetime.date(int(entry[0]), int(entry[1]), 1)
if not month in ledger_entries:
ledger_entries[month] = {}
ledger_entries[month]['credit'] = int(entry[2])
ledger_entries = OrderedDict(sorted(ledger_entries.items()))
account = foot_account(accountName, ledger_entries, 'Summary')
return [account, ledger_entries]
maxBytes=20 * 1024 * 1024,
backupCount=5,
encoding=None,
delay=0)
handler.setFormatter(formatter)
logger = logging.getLogger(__name__)
logger.addHandler(handler)
logger.setLevel(logging.DEBUG)
from pgsql_hourly import Data, Device, Tag, Base, Hourly, Daily, session, \
gw_list, device_list, tag_list
from sqlalchemy import types, and_, select
from sqlalchemy.sql import func, cast
hh = func.date_part('hour', cast(Hourly.stime, types.DateTime)).label('h')
dd = func.date_part('day', cast(Hourly.stime, types.DateTime)).label('d')
import dateutil.parser
def my_tstamp_strip(t):
return dateutil.parser.parse(t).date().isoformat()
#datetime.strptime(t,"%Y-%m-%d %H:%M:%S.%f").strftime("%Y-%m-%d %H:00")
def get_daily_last_row_stime():
# get last row time stamp
last_record = session.query(Daily).filter(
and_(
Daily.gateway == g,
def get_ledger(subaccount, currency, groupby, period_beg=None, period_end=None):
name = subaccount.name
if period_beg and period_end:
period_beg = datetime.strptime(period_beg, "%Y-%m-%d")
period_end = datetime.strptime(period_end, "%Y-%m-%d")
period_beg = datetime(period_beg.year, period_beg.month, period_beg.day, 0, 0, 0, 0)
period_end = datetime(period_end.year, period_end.month, period_end.day, 23, 59, 59, 999999)
if groupby == "All":
ledgers_query = db.session \
.query( \
models.LedgerEntries.ledger, \
models.LedgerEntries.debit, \
models.LedgerEntries.credit, \
models.LedgerEntries.date, \
models.LedgerEntries.journal_entry_id) \
.filter_by(currency=currency) \
.filter_by(ledger=name) \
.all()
elif groupby == "Daily":
ledgers_query = db.session \
.query( \
models.LedgerEntries.ledger, \
func.sum(models.LedgerEntries.debit), \
func.sum(models.LedgerEntries.credit), \
func.date_part('year', models.LedgerEntries.date), \
func.date_part('month', models.LedgerEntries.date), \
func.date_part('day', models.LedgerEntries.date)) \
.filter_by(currency=currency) \
.filter_by(ledger=name) \
.group_by( \
models.LedgerEntries.ledger, \
func.date_part('year', models.LedgerEntries.date), \
func.date_part('month', models.LedgerEntries.date), \
func.date_part('day', models.LedgerEntries.date)) \
.all()
elif groupby == "Weekly":
ledgers_query = db.session \
.query( \
models.LedgerEntries.ledger, \
func.sum(models.LedgerEntries.debit), \
func.sum(models.LedgerEntries.credit), \
func.date_part('year', models.LedgerEntries.date), \
func.date_part('week', models.LedgerEntries.date)) \
.filter_by(currency=currency) \
.filter_by(ledger=name) \
.group_by( \
models.LedgerEntries.ledger, \
func.date_part('year', models.LedgerEntries.date), \
func.date_part('week', models.LedgerEntries.date)) \
.all()
elif groupby == "Monthly":
ledgers_query = db.session \
.query( \
models.LedgerEntries.ledger, \
func.sum(models.LedgerEntries.debit), \
func.sum(models.LedgerEntries.credit), \
func.date_part('year', models.LedgerEntries.date), \
func.date_part('month', models.LedgerEntries.date)) \
.filter_by(currency=currency) \
.filter_by(ledger=name) \
.group_by( \
models.LedgerEntries.ledger, \
func.date_part('year', models.LedgerEntries.date), \
func.date_part('month', models.LedgerEntries.date)) \
.all()
subaccount.ledgers = []
subaccount.totalDebits = 0
subaccount.totalCredits = 0
subaccount.debitBalance = 0
subaccount.creditBalance = 0
for result in ledgers_query:
if result[0] == subaccount.name:
if groupby == "All":
ledger = result
elif groupby == "Daily":
ledger = [result[0], result[1], result[2], datetime(int(result[3]), int(result[4]), int(result[5]), 23, 59, 59, 999999)]
elif groupby == "Weekly":
ledger = [result[0], result[1], result[2], datetime.combine(Week(int(result[3]), int(result[4])).monday(),datetime.min.time())]
elif groupby == "Monthly":
lastday = calendar.monthrange(int(result[3]), int(result[4]))[1]
ledger = [result[0], result[1], result[2], datetime(int(result[3]), int(result[4]), lastday, 23, 59, 59, 999999)]
if period_beg and period_end:
if period_beg <= ledger[3] <= period_end:
subaccount.ledgers.append(ledger)
else:
subaccount.ledgers.append(ledger)
subaccount.totalDebits += ledger[1]
subaccount.totalCredits += ledger[2]
subaccount.ledgers = sorted(subaccount.ledgers, key=itemgetter(3))
if subaccount.totalDebits > subaccount.totalCredits:
subaccount.debitBalance = subaccount.totalDebits - subaccount.totalCredits
elif subaccount.totalDebits < subaccount.totalCredits:
subaccount.creditBalance = subaccount.totalCredits - subaccount.totalDebits
return subaccount
def trial_balance(currency, groupby, period):
if groupby == None:
period = datetime.now()
lastday = calendar.monthrange(period.year, period.month)[1]
year = period.year
month = period.month
period_beg = datetime(period.year, period.month, 1, 0, 0, 0, 0).strftime('%Y-%m-%d')
period_end = datetime(period.year, period.month, lastday, 23, 59, 59, 999999).strftime('%Y-%m-%d')
return redirect(
url_for('bookkeeping.trial_balance',
currency='Satoshis',
groupby='Monthly',
period='Current'))
if groupby == 'Daily':
periods = db.session \
.query(\
func.date_part('year', models.LedgerEntries.date), \
func.date_part('month', models.LedgerEntries.date), \
func.date_part('day', models.LedgerEntries.date)) \
.order_by( \
func.date_part('year', models.LedgerEntries.date).desc(), \
func.date_part('month', models.LedgerEntries.date).desc(), \
func.date_part('day', models.LedgerEntries.date).desc()) \
.group_by( \
func.date_part('year', models.LedgerEntries.date), \
func.date_part('month', models.LedgerEntries.date), \
func.date_part('day', models.LedgerEntries.date)) \
.limit(7)
periods = sorted([date(int(period[0]), int(period[1]), int(period[2])) for period in periods])
if period == 'Current':
period = periods[-1]
else:
period = datetime.strptime(period, "%Y-%m-%d")
period_beg = datetime(period.year, period.month, period.day, 0, 0, 0, 0)
period_end = datetime(period.year, period.month, period.day, 23, 59, 59, 999999)
periods = sorted([period.strftime("%Y-%m-%d") for period in periods])
period = period.strftime("%Y-%m-%d")
elif groupby == 'Weekly':
periods = db.session \
.query(\
func.date_part('year', models.LedgerEntries.date), \
func.date_part('week', models.LedgerEntries.date)) \
.order_by( \
func.date_part('year', models.LedgerEntries.date).desc(), \
func.date_part('week', models.LedgerEntries.date).desc()) \
.group_by( \
func.date_part('year', models.LedgerEntries.date), \
func.date_part('week', models.LedgerEntries.date)) \
.limit(7)
periods = sorted([Week(int(period[0]), int(period[1])).monday() for period in periods])
if period == 'Current':
period = periods[-1]
else:
period = period.split('-')
period = Week(int(period[0]), int(period[1])+1).monday()
period_beg = period - timedelta(days = period.weekday())
period_end = period_beg + timedelta(days = 6)
period_beg = datetime(period_beg.year, period_beg.month, period_beg.day, 0, 0, 0, 0)
period_end = datetime(period_end.year, period_end.month, period_end.day, 23, 59, 59, 999999)
periods = sorted([period.strftime("%Y-%W") for period in periods])
period = period.strftime("%Y-%W")
elif groupby == 'Monthly':
if period == 'Current':
period = datetime.now()
else:
period = datetime.strptime(period, "%Y-%m")
lastday = calendar.monthrange(period.year, period.month)[1]
period_beg = datetime(period.year, period.month, 1, 0, 0, 0, 0)
period_end = period = datetime(period.year, period.month, lastday, 23, 59, 59, 999999)
periods = db.session \
.query(\
func.date_part('year', models.LedgerEntries.date), \
func.date_part('month', models.LedgerEntries.date)) \
.group_by( \
func.date_part('year', models.LedgerEntries.date), \
func.date_part('month', models.LedgerEntries.date)) \
.all()
periods = sorted([date(int(period[0]), int(period[1]), 1) for period in periods])
periods = sorted([period.strftime("%Y-%m") for period in periods])
period = period.strftime("%Y-%m")
elif groupby == 'Annual':
if period == 'Current':
period = datetime.now()
else:
period = datetime.strptime(period, "%Y")
period_beg = datetime(period.year, 1, 1, 0, 0, 0, 0)
period_end = datetime(period.year, 12, 31, 23, 59, 59, 999999)
periods = db.session \
.query(func.date_part('year', models.LedgerEntries.date)) \
.group_by(func.date_part('year', models.LedgerEntries.date)) \
.all()
periods = sorted([date(int(period[0]), 12, 31) for period in periods])
periods = sorted([period.strftime("%Y") for period in periods])
period = period.strftime("%Y")
subaccounts = db.session \
.query( \
models.LedgerEntries.ledger, \
func.sum(models.LedgerEntries.debit), \
func.sum(models.LedgerEntries.credit)) \
.filter(models.LedgerEntries.currency==currency) \
.filter( models.LedgerEntries.date.between(period_beg, period_end)) \
.group_by(models.LedgerEntries.ledger) \
.all()
totalDebits = 0
totalCredits = 0
for subaccount in subaccounts:
totalDebits += subaccount[1]
totalCredits += subaccount[2]
return render_template('trial_balance.html',
groupby=groupby,
currency=currency,
periods=periods,
period=period,
period_beg=period_beg,
period_end=period_end,
totalDebits=totalDebits,
totalCredits=totalCredits,
subaccounts=subaccounts)
def query_entries(accountName, groupby, currency):
if groupby == "All":
ledger_entries = models.LedgerEntries \
.query \
.filter_by(ledger=accountName) \
.filter_by(currency=currency) \
.order_by(models.LedgerEntries.date.desc()) \
.order_by(models.LedgerEntries.tside.desc()) \
.all()
account = foot_account(accountName, ledger_entries, 'All')
elif groupby == "Daily":
debit_ledger_entries = db.session\
.query( \
func.date_part('year', models.LedgerEntries.date), \
func.date_part('month', models.LedgerEntries.date), \
func.date_part('day', models.LedgerEntries.date), \
func.sum(models.LedgerEntries.amount)) \
.filter_by(ledger=accountName)\
.filter_by(tside='debit')\
.filter_by(currency=currency)\
.group_by( \
func.date_part('year', models.LedgerEntries.date), \
func.date_part('month', models.LedgerEntries.date), \
func.date_part('day', models.LedgerEntries.date)) \
.all()
credit_ledger_entries = db.session \
.query( \
func.date_part('year', models.LedgerEntries.date), \
func.date_part('month', models.LedgerEntries.date), \
func.date_part('day', models.LedgerEntries.date), \
func.sum(models.LedgerEntries.amount))\
.filter_by(currency=currency) \
.filter_by(ledger=accountName) \
.filter_by(tside='credit') \
.group_by( \
func.date_part('year', models.LedgerEntries.date), \
func.date_part('month', models.LedgerEntries.date), \
func.date_part('day', models.LedgerEntries.date)) \
.all()
ledger_entries = {}
for entry in debit_ledger_entries:
day = datetime.date(int(entry[0]), int(entry[1]), int(entry[2]))
if not day in ledger_entries:
ledger_entries[day] = {}
ledger_entries[day]['debit'] = int(entry[3])
for entry in credit_ledger_entries:
day = datetime.date(int(entry[0]), int(entry[1]), int(entry[2]))
if not day in ledger_entries:
ledger_entries[day] = {}
ledger_entries[day]['credit'] = int(entry[3])
ledger_entries = OrderedDict(sorted(ledger_entries.items()))
account = foot_account(accountName, ledger_entries, 'Summary')
elif groupby == "Monthly":
debit_ledger_entries = db.session \
.query( \
func.date_part('year', models.LedgerEntries.date), \
func.date_part('month', models.LedgerEntries.date), \
func.sum(models.LedgerEntries.amount)) \
.filter_by(ledger=accountName) \
.filter_by(currency=currency) \
.filter_by(tside='debit') \
.group_by( \
func.date_part('year', models.LedgerEntries.date), \
func.date_part('month', models.LedgerEntries.date)) \
.all()
credit_ledger_entries = db.session\
.query( \
func.date_part('year', models.LedgerEntries.date), \
func.date_part('month', models.LedgerEntries.date), \
func.sum(models.LedgerEntries.amount)) \
.filter_by(ledger=accountName) \
.filter_by(currency=currency) \
.filter_by(tside='credit') \
.group_by( \
func.date_part('year', models.LedgerEntries.date), \
func.date_part('month', models.LedgerEntries.date)) \
.all()
ledger_entries = {}
for entry in debit_ledger_entries:
month = datetime.date(int(entry[0]), int(entry[1]), 1)
if not month in ledger_entries:
ledger_entries[month] = {}
ledger_entries[month]['debit'] = int(entry[2])
for entry in credit_ledger_entries:
month = datetime.date(int(entry[0]), int(entry[1]), 1)
if not month in ledger_entries:
ledger_entries[month] = {}
ledger_entries[month]['credit'] = int(entry[2])
ledger_entries = OrderedDict(sorted(ledger_entries.items()))
account = foot_account(accountName, ledger_entries, 'Summary')
return [account, ledger_entries]