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