def update_cdi():
cdi_file = open("import\\cdi.xls", "r")
lines = [
line.replace('\n', '').split('\t') for line in cdi_file.readlines()
]
lines.pop(0)
conn = sqlite3.connect('./db/cs50.db')
c = conn.cursor()
c.execute("delete from cdi")
for line in lines:
date = datetime.strptime(line[0], '%d/%m/%Y').date()
c.execute(
"insert into cdi (date, value) values (?, ?)",
(date,
(float(line[4].replace('.', '').replace(',', '.')) - 1) * 100))
initial_date = one_year_ago(first_day_of_month(datetime.today().date()))
final_date = last_day_of_month(last_month())
c.execute(
"""update indicators set
Cdi12Months = (select sum(value) from cdi where date >= ? and date <= ?),
CdiLastMonth = (select sum(value) from cdi where strftime('%Y%m', date) = ?),
CdiMonthName = ?""",
(initial_date, final_date, last_month().strftime('%Y%m'),
month_name(last_month().month)))
conn.commit()
def update_selic():
#https://api.bcb.gov.br/dados/serie/bcdata.sgs.11/dados?formato=json&dataInicial=21/10/2019&dataFinal=21/10/2020
url = "https://api.bcb.gov.br/dados/serie/bcdata.sgs.11/dados"
initial_date = one_year_ago(first_day_of_month(datetime.today().date()))
final_date = last_day_of_month(last_month())
querystring = {
"formato": "json",
"dataInicial": initial_date.strftime("%d/%m/%Y"),
"dataFinal": final_date.strftime("%d/%m/%Y")
}
response = requests.request("GET", url, params=querystring)
resp = response.json()
conn = sqlite3.connect('./db/cs50.db')
c = conn.cursor()
c.execute("delete from selic")
for selic in resp:
date = datetime.strptime(selic["data"], '%d/%m/%Y').date()
c.execute("insert into selic (date, value) values (?, ?)",
(date, selic["valor"]))
c.execute(
"""update indicators set
Selic12Months = (select sum(value) from selic where date >= ? and date <= ?),
SelicLastMonth = (select sum(value) from selic where strftime('%Y%m', date) = ?),
SelicMonthName = ?""",
(initial_date, final_date, last_month().strftime('%Y%m'),
month_name(last_month().month)))
conn.commit()
def apply_grace_period(revrec_schedule, item, payment_date):
"""Adjusts the specified revrec schedule for late payment, which requires journal entries
related to grace period.
:param revrec_schedule: Dictionary of debits and credits by day to be adjusted due to
application of grace period.
:param item: InvoiceItem object
:param payment_date: Date of payment
:return list. Supporting notes on grace period calculations. Displayed in UI output.
"""
gp_notes = []
service_start = item.service_start
service_end = item.service_end
# Identify the details of the prev paid service period.
shift = {'Monthly': 30, 'Yearly': 365, 'Biyearly': 730}
prev_service_end = day_before(service_start)
prev_service_start = prev_service_end - timedelta(shift[item.billperiod] - 1)
prev_service_term = days_elapsed(prev_service_start, prev_service_end)
prev_amount = item.total_amount
prev_amort = prev_amount / prev_service_term
# Financial reporting days. Important for determining debit and credit entries.
current_reporting_day = datetime(service_start.year, service_start.month,
last_day_of_month(service_start.year, service_start.month))
prev_reporting_day = day_before(datetime(prev_service_end.year, prev_service_end.month, 1))
# Notes for display in the UI output.
gp_notes.append('JOURNAL ENTRIES FOR GRACE PERIOD')
gp_notes.append('---'*60)
gp_notes.append('payment date: %s, current term: %s -> %s, prev term: %s -> %s' % (
pretty_date(payment_date),
pretty_date(service_start),
pretty_date(service_end),
pretty_date(prev_service_start),
pretty_date(prev_service_end)))
gp_notes.append('current reporting day: %s, prev reporting day: %s, days reserved for: %s -> %s' % (
pretty_date(current_reporting_day),
pretty_date(prev_reporting_day),
prev_service_start,
prev_reporting_day))
# For each date that payment is late...
running_total_dr_reserve = 0
for date in daterange(service_start, day_before(payment_date)):
# The previous service term is extended by the grace period used
revised_service_term = prev_service_term + days_elapsed(service_start, date)
# Revenue amortization for the extended service term
revised_amort = prev_amount / revised_service_term
# Difference in the daily amortization for the previous service term
amort_difference = prev_amort - revised_amort
# The difference b/n revenue that should have been recognized vs. what was recognized in the period
# prior to the previous reporting day. This amount has already been reserved for, so the journal
# entry is a debit against the reserve for grace periods.
revised_dr_reserve_graceperiod = days_elapsed(prev_service_start, prev_reporting_day) * amort_difference
# We have been debiting the reserve for each day late, this day's debit amount is equal to the
# incremental debit against the reserve. The other side of the entry is credit contra-revenue.
dr_reserve_graceperiod = revised_dr_reserve_graceperiod - running_total_dr_reserve
cr_contra_rev = dr_reserve_graceperiod
revrec_schedule[date]['dr_reserve_graceperiod'] = dr_reserve_graceperiod
revrec_schedule[date]['cr_contra_rev'] = cr_contra_rev
gp_notes.append('%s, PrevTerm: %s->%s, PrevAmort: $%s->$%s, Value: %s, DaysReservedFor: %s, \
PrevTotalReserve %s, TotalReserve: %s, DR reserve: %s' % (
pretty_date(date),
prev_service_term,
revised_service_term,
round(prev_amort, 3),
round(revised_amort, 3),
revised_service_term * revised_amort,
(prev_reporting_day - prev_service_start).days + 1,
round(running_total_dr_reserve, 3),
round(revised_dr_reserve_graceperiod, 3),
round(dr_reserve_graceperiod,3)))
running_total_dr_reserve += dr_reserve_graceperiod
gp_notes.append('---'*60)
return gp_notes
def apply_grace_period(revrec_schedule, item, payment_date):
"""Adjusts the specified revrec schedule for late payment, which requires journal entries
related to grace period.
:param revrec_schedule: Dictionary of debits and credits by day to be adjusted due to
application of grace period.
:param item: InvoiceItem object
:param payment_date: Date of payment
:return list. Supporting notes on grace period calculations. Displayed in UI output.
"""
gp_notes = []
service_start = item.service_start
service_end = item.service_end
# Identify the details of the prev paid service period.
shift = {'Monthly': 30, 'Yearly': 365, 'Biyearly': 730}
prev_service_end = day_before(service_start)
prev_service_start = prev_service_end - timedelta(shift[item.billperiod] -
1)
prev_service_term = days_elapsed(prev_service_start, prev_service_end)
prev_amount = item.total_amount
prev_amort = prev_amount / prev_service_term
# Financial reporting days. Important for determining debit and credit entries.
current_reporting_day = datetime(
service_start.year, service_start.month,
last_day_of_month(service_start.year, service_start.month))
prev_reporting_day = day_before(
datetime(prev_service_end.year, prev_service_end.month, 1))
# Notes for display in the UI output.
gp_notes.append('JOURNAL ENTRIES FOR GRACE PERIOD')
gp_notes.append('---' * 60)
gp_notes.append(
'payment date: %s, current term: %s -> %s, prev term: %s -> %s' %
(pretty_date(payment_date), pretty_date(service_start),
pretty_date(service_end), pretty_date(prev_service_start),
pretty_date(prev_service_end)))
gp_notes.append(
'current reporting day: %s, prev reporting day: %s, days reserved for: %s -> %s'
% (pretty_date(current_reporting_day), pretty_date(prev_reporting_day),
prev_service_start, prev_reporting_day))
# For each date that payment is late...
running_total_dr_reserve = 0
for date in daterange(service_start, day_before(payment_date)):
# The previous service term is extended by the grace period used
revised_service_term = prev_service_term + days_elapsed(
service_start, date)
# Revenue amortization for the extended service term
revised_amort = prev_amount / revised_service_term
# Difference in the daily amortization for the previous service term
amort_difference = prev_amort - revised_amort
# The difference b/n revenue that should have been recognized vs. what was recognized in the period
# prior to the previous reporting day. This amount has already been reserved for, so the journal
# entry is a debit against the reserve for grace periods.
revised_dr_reserve_graceperiod = days_elapsed(
prev_service_start, prev_reporting_day) * amort_difference
# We have been debiting the reserve for each day late, this day's debit amount is equal to the
# incremental debit against the reserve. The other side of the entry is credit contra-revenue.
dr_reserve_graceperiod = revised_dr_reserve_graceperiod - running_total_dr_reserve
cr_contra_rev = dr_reserve_graceperiod
revrec_schedule[date][
'dr_reserve_graceperiod'] = dr_reserve_graceperiod
revrec_schedule[date]['cr_contra_rev'] = cr_contra_rev
gp_notes.append(
'%s, PrevTerm: %s->%s, PrevAmort: $%s->$%s, Value: %s, DaysReservedFor: %s, \
PrevTotalReserve %s, TotalReserve: %s, DR reserve: %s'
% (pretty_date(date), prev_service_term, revised_service_term,
round(prev_amort, 3), round(
revised_amort, 3), revised_service_term * revised_amort,
(prev_reporting_day - prev_service_start).days + 1,
round(running_total_dr_reserve,
3), round(revised_dr_reserve_graceperiod,
3), round(dr_reserve_graceperiod, 3)))
running_total_dr_reserve += dr_reserve_graceperiod
gp_notes.append('---' * 60)
return gp_notes
def test_last_day_of_month(self):
for year, month in zip([2012, 2013, 2011], [2, 2, 7]):
self.assertEqual(helpers.last_day_of_month(year, month),
calendar.monthrange(year, month)[1])