def balance_trends(params):
dt = parse(params['date'])
accounts = api_func('gl', 'account')
if 'accts_path' in params:
acct_list = [x['id'] for x in accounts if params['accts_path'] in x['path']]
else:
acct_list = params['acct_list'].split('.')
gl_strategy = params.get('gl_strategy', None)
company_id = params.get('company_id', utils.get_default_company())
# 3 months for now
report = get_report('AccountActivity', company_id, version='v1')
report.set_gl_strategy(gl_strategy)
M_1 = utils.end_of_prev_month(dt.month, dt.year)
M_2 = utils.end_of_prev_month(M_1.month, M_1.year)
three_mth = {}
three_mth['M_0'] = '%dM%s' % (dt.year, '{:02d}'.format(dt.month))
three_mth['M_1'] = '%dM%s' % (M_1.year, '{:02d}'.format(M_1.month))
three_mth['M_2'] = '%dM%s' % (M_2.year, '{:02d}'.format(M_2.month))
three_mth_order = ['M_2','M_1','M_0']
col_tag = '%dM%s' % (dt.year, '{:02d}'.format(dt.month))
report.configure(col_tag=col_tag)
report.columns = three_mth
report.column_order = three_mth_order
report.acct_list = acct_list
return [x for x in report.calcs() if x['fmt_tag']!='header']
def balance_trends(params):
dt = parse(params['date'])
accounts = api_func('gl', 'account')
if 'accts_path' in params:
acct_list = [x['id'] for x in accounts if params['accts_path'] in x['path']]
else:
acct_list = params['acct_list'].split('.')
gl_strategy = params.get('gl_strategy', None)
company_id = params.get('company_id', utils.get_default_company())
# 3 months for now
report = get_report('AccountActivity', company_id, version='v1')
report.set_gl_strategy(gl_strategy)
M_1 = utils.end_of_prev_month(dt.month, dt.year)
M_2 = utils.end_of_prev_month(M_1.month, M_1.year)
three_mth = {}
three_mth['M_0'] = '%dM%s' % (dt.year, '{:02d}'.format(dt.month))
three_mth['M_1'] = '%dM%s' % (M_1.year, '{:02d}'.format(M_1.month))
three_mth['M_2'] = '%dM%s' % (M_2.year, '{:02d}'.format(M_2.month))
three_mth_order = ['M_2','M_1','M_0']
col_tag = '%dM%s' % (dt.year, '{:02d}'.format(dt.month))
report.configure(col_tag=col_tag)
report.columns = three_mth
report.column_order = three_mth_order
report.acct_list = acct_list
return [x for x in report.calcs() if x['fmt_tag']!='header']
def forecast_run_task(fcast_id,
report_id,
col_tag,
company_ID=utils.get_default_company(),
version='v1'):
fcast = Forecast.objects.get(id=fcast_id)
strategy = QueryManagerStrategyFactory().get('forecast')
strategy.set_cache(fcast_id=fcast_id,
proj_gl_entries=fcast.get_gl_entries())
report = accountifie.reporting.rptutils.get_report(report_id,
company_ID,
version=version)
report.configure(col_tag=col_tag)
report.set_gl_strategy(strategy)
report_data = report.calcs()
path = os.path.join(settings.DATA_ROOT,
'forecast_%s_%s.csv' % (fcast_id, report_id))
f = open(path, 'wb')
writer = csv.writer(f)
writer.writerow([''] + report.column_order)
map_values = lambda x: '' if x == '' else str(x['text'])
for row in report_data:
writer.writerow([row['label']] + [
map_values(row[col])
for col in [x for x in report.column_order if x in row]
])
f.close()
def external_bals_history(qstring={}):
dt = parse(qstring.get('date', datetime.datetime.now().isoformat())).date()
company_id = qstring.get('company_id', utils.get_default_company())
gl_strategy = qstring.get('gl_strategy', None)
acct = qstring.get('acct',None)
dts = list(rrule(DAILY, dtstart=dt + datetime.timedelta(days=-7), until=dt, byweekday=(MO,TU,WE,TH,FR)))
dts = [dt.date() for dt in dts]
external_balances = dict((x.date.isoformat(), float(x.balance)) for x in ExternalBalance.objects.filter(account__id=acct).filter(date__in=dts))
int_bals_df = accountifie.query.query_manager.QueryManager(gl_strategy).pd_acct_balances(company_id, dict((dt.isoformat(),dt) for dt in dts), acct_list=[acct])
if acct in int_bals_df.index:
internal_balances = accountifie.query.query_manager.QueryManager(gl_strategy).pd_acct_balances(company_id, dict((dt.isoformat(),dt) for dt in dts), acct_list=[acct]).loc[acct].to_dict()
else:
internal_balances = {}
bal_checks = []
for dt in dts:
external = external_balances.get(dt.isoformat(), 0.0)
internal = internal_balances.get(dt.isoformat(), 0.0)
bal_checks.append({'Date': dt, 'Internal': internal, 'External': external, 'Diff': external - internal})
return bal_checks
def large_expenses(dt):
data_url = "/base/api/large_expenses/?date=%s&company=%s" % (dt, get_default_company())
row_defs = [{'data_field': 'id', 'value': 'ID', 'formatter': 'nameFormatter'},
{'data_field': 'expense_date', 'value': 'Expense Date', 'formatter': 'nameFormatter'},
{'data_field': 'counterparty_name', 'value': 'Vendor', 'formatter': 'nameFormatter'},
{'data_field': 'start_date', 'value': 'Expense Start', 'formatter': 'nameFormatter'},
{'data_field': 'end_date', 'value': 'Expense End', 'formatter': 'nameFormatter'},
{'data_field': 'amount_fmt', 'value': 'Amount', 'formatter': 'nameFormatter'},
{'data_field': 'expense_report_name', 'value': 'Expense Report', 'formatter': 'nameFormatter'},
]
return get_bstrap_table(data_url, row_defs)
def trans_detail(self, trans_list, company_id=utils.get_default_company()):
cache = glcache.get_cache(company_id)
entries = cache.get_gl_entries_trans(trans_list)
entries['date_end'] = entries.apply(
lambda row: row['date_end']
if row['date_end'] is not None else row['date'],
axis=1)
start_date = min(min(entries['date']), min(entries['date_end']))
end_date = max(max(entries['date']), max(entries['date_end']))
start_mth = start_date.month
start_yr = start_date.year
end_mth = end_date.month
end_yr = end_date.year
dt = utils.start_of_month(start_date.month, start_date.year)
dates = {dt.isoformat(): dt}
dt1 = start_date
dt2 = end_date
start_month = dt1.month
end_months = (dt2.year - dt1.year) * 12 + dt2.month + 1
date_list = [
utils.end_of_month(mn, yr)
for (yr, mn) in (((m - 1) / 12 + dt1.year, (m - 1) % 12 + 1)
for m in range(start_month, end_months))
]
date_list = list(set([start_date, end_date] + date_list))
dates = dict((d.isoformat(), d) for d in date_list)
dates[datetime.date(start_yr, start_month,
1).isoformat()] = datetime.date(
start_yr, start_month, 1)
data = {}
for dt in dates:
start, end = utils.get_dates(dates[dt])
sub_entries = self.deprec_calcs(start, end, entries)
col = sub_entries[['account_id',
'amount']].groupby('account_id').sum()['amount']
data[dt] = col[col != 0.0]
return pd.DataFrame(data).fillna(0.0).T
def forecast_run_task(fcast_id, report_id, col_tag, company_ID=utils.get_default_company(), version='v1'):
fcast = Forecast.objects.get(id=fcast_id)
strategy = QueryManagerStrategyFactory().get('forecast')
strategy.set_cache(fcast_id=fcast_id, proj_gl_entries=fcast.get_gl_entries())
report = accountifie.reporting.rptutils.get_report(report_id, company_ID, version=version)
report.configure(col_tag=col_tag)
report.set_gl_strategy(strategy)
report_data = report.calcs()
path = os.path.join(settings.DATA_ROOT, 'forecast_%s_%s.csv' %( fcast_id, report_id))
f = open(path, 'wb')
writer = csv.writer(f)
writer.writerow([''] + report.column_order)
map_values = lambda x: '' if x=='' else str(x['text'])
for row in report_data:
writer.writerow([row['label']] + [map_values(row[col]) for col in [x for x in report.column_order if x in row]])
f.close()
def trans_detail(self, trans_list, company_id=utils.get_default_company()):
cache = glcache.get_cache(company_id)
entries = cache.get_gl_entries_trans(trans_list)
entries['date_end'] = entries.apply(lambda row: row['date_end'] if row['date_end'] is not None else row['date'],axis=1)
start_date = min(min(entries['date']), min(entries['date_end']))
end_date = max(max(entries['date']), max(entries['date_end']))
start_mth = start_date.month
start_yr = start_date.year
end_mth = end_date.month
end_yr = end_date.year
dt = utils.start_of_month(start_date.month, start_date.year)
dates = {dt.isoformat(): dt}
dt1 = start_date
dt2 = end_date
start_month=dt1.month
end_months=(dt2.year-dt1.year)*12 + dt2.month+1
date_list = [utils.end_of_month(mn,yr) for (yr, mn) in (
((m - 1) / 12 + dt1.year, (m - 1) % 12 + 1) for m in range(start_month, end_months)
)]
date_list = list(set([start_date,end_date] + date_list))
dates = dict((d.isoformat(), d) for d in date_list)
dates[datetime.date(start_yr,start_month,1).isoformat()] = datetime.date(start_yr,start_month,1)
data = {}
for dt in dates:
start, end = utils.get_dates(dates[dt])
sub_entries = self.deprec_calcs(start, end, entries)
col = sub_entries[['account_id','amount']].groupby('account_id').sum()['amount']
data[dt] = col[col!=0.0]
return pd.DataFrame(data).fillna(0.0).T
def external_bals_history(qstring={}):
dt = parse(qstring.get('date', datetime.datetime.now().isoformat())).date()
company_id = qstring.get('company_id', utils.get_default_company())
gl_strategy = qstring.get('gl_strategy', None)
acct = qstring.get('acct', None)
dts = list(
rrule(DAILY,
dtstart=dt + datetime.timedelta(days=-7),
until=dt,
byweekday=(MO, TU, WE, TH, FR)))
dts = [dt.date() for dt in dts]
external_balances = dict((x.date.isoformat(), float(x.balance))
for x in ExternalBalance.objects.filter(
account__id=acct).filter(date__in=dts))
internal_balances = accountifie.query.query_manager.QueryManager(
gl_strategy).pd_acct_balances(company_id,
dict((dt.isoformat(), dt) for dt in dts),
acct_list=[acct]).loc[acct].to_dict()
cols = {dt.isoformat(): dt}
bal_checks = []
for dt in dts:
external = external_balances.get(dt.isoformat(), 0.0)
internal = internal_balances.get(dt.isoformat(), 0.0)
bal_checks.append({
'Date': dt,
'Internal': internal,
'External': external,
'Diff': external - internal
})
return bal_checks
