def get_data_column(filters, partner_doctype): data = [] period_list = get_period_list(filters.fiscal_year, filters.fiscal_year, '', '', 'Fiscal Year', filters.period, company=filters.company) rows = get_data(filters, period_list, partner_doctype) columns = get_columns(filters, period_list, partner_doctype) if not rows: return columns, data for key, value in rows.items(): value.update({ frappe.scrub(partner_doctype): key[0], 'item_group': key[1] }) data.append(value) return columns, data
def execute(filters=None): period_list = get_period_list(filters.fiscal_year, filters.periodicity) income = get_data(filters.company, "Income", "Credit", period_list, accumulated_values=filters.accumulated_values, ignore_closing_entries=True) expense = get_data(filters.company, "Expense", "Debit", period_list, accumulated_values=filters.accumulated_values, ignore_closing_entries=True) net_profit_loss = get_net_profit_loss(income, expense, period_list, filters.company) data = [] data.extend(income or []) data.extend(expense or []) if net_profit_loss: data.append(net_profit_loss) columns = get_columns(filters.periodicity, period_list, filters.accumulated_values, filters.company) chart = get_chart_data(filters, columns, income, expense, net_profit_loss) return columns, data, None, chart
def execute(filters=None): columns, data, message, chart = [], [], [], [] if not filters.get('company'): return columns, data, message, chart if filters.get('compare_with_company') and not (filters.from_company or filters.to_company): frappe.msgprint(_("Please select From Company and To Company")) period_list = get_period_list(filters.from_date, filters.to_date, filters.periodicity, filters.accumulated_in_group_company) companies_column, companies = get_companies(filters) columns = get_columns(companies_column, filters.periodicity, period_list, filters, filters.accumulated_in_group_company) if filters.get('report') == "Balance Sheet": data, message, chart = get_balance_sheet_data(period_list, companies, columns, filters) elif filters.get('report') == "Profit and Loss Statement": data, message, chart = get_profit_loss_data(period_list, companies, columns, filters) else: if cint( frappe.db.get_single_value('Accounts Settings', 'use_custom_cash_flow')): from erpnext.accounts.report.cash_flow.custom_cash_flow import execute as execute_custom return execute_custom(filters=filters) data = get_cash_flow_data(period_list, companies, filters) return columns, data, message, chart
def execute(filters=None): if cint(frappe.db.get_single_value('Accounts Settings', 'use_custom_cash_flow')): from erpnext.accounts.report.cash_flow.custom_cash_flow import execute as execute_custom return execute_custom(filters=filters) period_list = get_period_list(filters.from_fiscal_year, filters.to_fiscal_year, filters.periodicity, filters.accumulated_values, filters.company) cash_flow_accounts = get_cash_flow_accounts() # compute net profit / loss income = get_data(filters.company, "Income", "Credit", period_list, filters=filters, accumulated_values=filters.accumulated_values, ignore_closing_entries=True, ignore_accumulated_values_for_fy= True) expense = get_data(filters.company, "Expense", "Debit", period_list, filters=filters, accumulated_values=filters.accumulated_values, ignore_closing_entries=True, ignore_accumulated_values_for_fy= True) net_profit_loss = get_net_profit_loss(income, expense, period_list, filters.company) data = [] company_currency = frappe.get_cached_value('Company', filters.company, "default_currency") for cash_flow_account in cash_flow_accounts: section_data = [] data.append({ "account_name": cash_flow_account['section_header'], "parent_account": None, "indent": 0.0, "account": cash_flow_account['section_header'] }) if len(data) == 1: # add first net income in operations section if net_profit_loss: net_profit_loss.update({ "indent": 1, "parent_account": cash_flow_accounts[0]['section_header'] }) data.append(net_profit_loss) section_data.append(net_profit_loss) for account in cash_flow_account['account_types']: account_data = get_account_type_based_data(filters.company, account['account_type'], period_list, filters.accumulated_values) account_data.update({ "account_name": account['label'], "account": account['label'], "indent": 1, "parent_account": cash_flow_account['section_header'], "currency": company_currency }) data.append(account_data) section_data.append(account_data) add_total_row_account(data, section_data, cash_flow_account['section_footer'], period_list, company_currency) add_total_row_account(data, data, _("Net Change in Cash"), period_list, company_currency) columns = get_columns(filters.periodicity, period_list, filters.accumulated_values, filters.company) return columns, data
def execute(filters=None): period_list = get_period_list(filters.fiscal_year, filters.periodicity, from_beginning=True) asset = get_data(filters.company, "Asset", "Debit", period_list, filters.accumulated_value) liability = get_data(filters.company, "Liability", "Credit", period_list, filters.accumulated_value) equity = get_data(filters.company, "Equity", "Credit", period_list, filters.accumulated_value) provisional_profit_loss = get_provisional_profit_loss( asset, liability, equity, period_list) data = [] data.extend(asset or []) data.extend(liability or []) data.extend(equity or []) if provisional_profit_loss: data.append(provisional_profit_loss) columns = get_columns(filters.periodicity, period_list, filters.accumulated_value) return columns, data
def execute(filters=None): period_list = get_period_list(filters.from_fiscal_year, filters.to_fiscal_year, filters.period_start_date, filters.period_end_date, filters.filter_based_on, filters.periodicity, company=filters.company) income = get_data(filters.company, "Income", "Credit", period_list, filters = filters, accumulated_values=filters.accumulated_values, ignore_closing_entries=True, ignore_accumulated_values_for_fy= True) expense = get_data(filters.company, "Expense", "Debit", period_list, filters=filters, accumulated_values=filters.accumulated_values, ignore_closing_entries=True, ignore_accumulated_values_for_fy= True) net_profit_loss = get_net_profit_loss(income, expense, period_list, filters.company, filters.presentation_currency) data = [] data.extend(income or []) data.extend(expense or []) if net_profit_loss: data.append(net_profit_loss) columns = get_columns(filters.periodicity, period_list, filters.accumulated_values, filters.company) chart = get_chart_data(filters, columns, income, expense, net_profit_loss) currency = filters.presentation_currency or frappe.get_cached_value('Company', filters.company, "default_currency") report_summary = get_report_summary(period_list, filters.periodicity, income, expense, net_profit_loss, currency) return columns, data, None, chart, report_summary
def execute(filters=None): if not filters.periodicity: filters.periodicity = "Monthly" period_list = get_period_list( filters.from_fiscal_year, filters.to_fiscal_year, filters.periodicity, filters.accumulated_values, filters.company ) mappers = get_mappers_from_db() cash_flow_accounts = setup_mappers(mappers) # compute net profit / loss income = get_data( filters.company, "Income", "Credit", period_list, filters=filters, accumulated_values=filters.accumulated_values, ignore_closing_entries=True, ignore_accumulated_values_for_fy=True ) expense = get_data( filters.company, "Expense", "Debit", period_list, filters=filters, accumulated_values=filters.accumulated_values, ignore_closing_entries=True, ignore_accumulated_values_for_fy=True ) net_profit_loss = get_net_profit_loss(income, expense, period_list, filters.company) company_currency = frappe.get_cached_value('Company', filters.company, "default_currency") data = compute_data(filters, company_currency, net_profit_loss, period_list, mappers, cash_flow_accounts) _add_total_row_account(data, data, _("Net Change in Cash"), period_list, company_currency) columns = get_columns(filters.periodicity, period_list, filters.accumulated_values, filters.company) return columns, data
def execute(filters=None): period_list = get_period_list(filters.from_date, filters.to_date, filters.periodicity, filters.accumulated_values, filters.company) ignore_accumulated_values_for_fy = True if filters.periodicity == "Custom": ignore_accumulated_values_for_fy=False income = get_data(filters.company, "Income", "Credit", period_list, filters=filters, accumulated_values=filters.accumulated_values, ignore_closing_entries=True, ignore_accumulated_values_for_fy=ignore_accumulated_values_for_fy) expense = get_data(filters.company, "Expense", "Debit", period_list, filters=filters, accumulated_values=filters.accumulated_values, ignore_closing_entries=True, ignore_accumulated_values_for_fy=ignore_accumulated_values_for_fy) net_profit_loss = get_net_profit_loss(income, expense, period_list, filters.company, filters.presentation_currency) data = [] data.extend(income or []) data.extend(expense or []) if net_profit_loss: data.append(net_profit_loss) columns = get_columns(filters.periodicity, period_list, filters.accumulated_values, filters.company) chart = get_chart_data(filters, columns, income, expense, net_profit_loss) return columns, data, None, chart
def execute(filters=None): period_list = get_period_list(filters.from_fiscal_year, filters.to_fiscal_year, filters.periodicity, filters.accumulated_values, filters.company) income = get_data(filters.company, "Income", "Credit", period_list, filters = filters, accumulated_values=filters.accumulated_values, ignore_closing_entries=True, ignore_accumulated_values_for_fy= True) expense = get_data(filters.company, "Expense", "Debit", period_list, filters=filters, accumulated_values=filters.accumulated_values, ignore_closing_entries=True, ignore_accumulated_values_for_fy= True) net_profit_loss = get_net_profit_loss(income, expense, period_list, filters.company) data = [] data.extend(income or []) data.extend(expense or []) if net_profit_loss: data.append(net_profit_loss) columns = get_columns(filters.periodicity, period_list, filters.accumulated_values, filters.company) chart = get_chart_data(filters, columns, income, expense, net_profit_loss) return columns, data, None, chart
def prepare_periodical_data(self): self.period_wise_data = {} from_date = add_years(self.filters.from_date, cint(self.filters.no_of_years) * -1) self.period_list = get_period_list(from_date, self.filters.to_date, from_date, self.filters.to_date, "Date Range", self.filters.periodicity, ignore_fiscal_year=True) order_data = self.get_data_for_forecast() or [] for entry in order_data: key = (entry.item_code, entry.warehouse) if key not in self.period_wise_data: self.period_wise_data[key] = entry period_data = self.period_wise_data[key] for period in self.period_list: # check if posting date is within the period if (entry.posting_date >= period.from_date and entry.posting_date <= period.to_date): period_data[period.key] = period_data.get(period.key, 0.0) + flt(entry.get(self.based_on_field)) for key, value in self.period_wise_data.items(): list_of_period_value = [value.get(p.key, 0) for p in self.period_list] if list_of_period_value: total_qty = [1 for d in list_of_period_value if d] if total_qty: value["avg"] = flt(sum(list_of_period_value)) / flt(sum(total_qty))
def prepare_chart_data(data, filters): labels_values_map = {} date_field = frappe.scrub(filters.date_based_on) period_list = get_period_list(filters.from_fiscal_year, filters.to_fiscal_year, filters.from_date, filters.to_date, filters.filter_based_on, "Monthly", company=filters.company) for d in period_list: labels_values_map.setdefault(d.get('label'), frappe._dict({'asset_value': 0, 'depreciated_amount': 0})) for d in data: date = d.get(date_field) belongs_to_month = formatdate(date, "MMM YYYY") labels_values_map[belongs_to_month].asset_value += d.get("asset_value") labels_values_map[belongs_to_month].depreciated_amount += d.get("depreciated_amount") return { "data" : { "labels": labels_values_map.keys(), "datasets": [ { 'name': _('Asset Value'), 'values': [d.get("asset_value") for d in labels_values_map.values()] }, { 'name': _('Depreciatied Amount'), 'values': [d.get("depreciated_amount") for d in labels_values_map.values()] } ] }, "type": "bar", "barOptions": { "stacked": 1 }, }
def get(filters=None): filters = frappe._dict({ "from_fiscal_year" : filters.get("from_fiscal_year"), "to_fiscal_year" : filters.get("to_fiscal_year"), "periodicity": filters.get("periodicity"), "accumulated_values": 1, "company": filters.get("company") }) period_list = get_period_list(filters.from_fiscal_year, filters.to_fiscal_year, filters.periodicity, filters.accumulated_values, filters.company) income = get_data(filters.company, "Income", "Credit", period_list, filters = filters, accumulated_values=filters.accumulated_values, ignore_closing_entries=True, ignore_accumulated_values_for_fy= True) expense = get_data(filters.company, "Expense", "Debit", period_list, filters=filters, accumulated_values=filters.accumulated_values, ignore_closing_entries=True, ignore_accumulated_values_for_fy= True) net_profit_loss = get_net_profit_loss(income, expense, period_list, filters.company, filters.presentation_currency) columns = get_columns(filters.periodicity, period_list, filters.accumulated_values, filters.company) chart = get_chart_data(filters, columns, income, expense, net_profit_loss) return chart
def execute(filters=None): columns, data, message, chart = [], [], [], [] if not filters.get('company'): return columns, data, message, chart period_list = get_period_list(filters.from_date, filters.to_date, filters.periodicity, filters.accumulated_in_group_company) if not filters.get('cost_center'): frappe.msgprint(_("Please select at least one cost center.")) if not filters.get('include_child_cost_centers'): cost_centers = filters.cost_center else: cost_centers = get_cost_centers_with_children(filters.cost_center) columns = get_columns(cost_centers, filters.periodicity, period_list) if filters.get('report') == "Balance Sheet": data, message, chart = get_balance_sheet_data(period_list, cost_centers, columns, filters, cost_center_wise=True) elif filters.get('report') == "Profit and Loss Statement": data, message, chart = get_profit_loss_data(period_list, cost_centers, columns, filters, cost_center_wise=True) return columns, data, message, chart
def execute(filters=None): period_list = get_period_list(filters.from_fiscal_year, filters.to_fiscal_year, filters.periodicity) asset = get_data(filters.company, "Asset", "Debit", period_list, only_current_fiscal_year=False) liability = get_data(filters.company, "Liability", "Credit", period_list, only_current_fiscal_year=False) equity = get_data(filters.company, "Equity", "Credit", period_list, only_current_fiscal_year=False) provisional_profit_loss, total_credit = get_provisional_profit_loss( asset, liability, equity, period_list, filters.company) message, opening_balance = check_opening_balance(asset, liability, equity) data = [] data.extend(asset or []) data.extend(liability or []) data.extend(equity or []) if opening_balance and round(opening_balance, 2) != 0: unclosed = { "account_name": "'" + _("Unclosed Fiscal Years Profit / Loss (Credit)") + "'", "account": None, "warn_if_negative": True, "currency": frappe.db.get_value("Company", filters.company, "default_currency") } for period in period_list: unclosed[period.key] = opening_balance provisional_profit_loss[period.key] = provisional_profit_loss[ period.key] - opening_balance unclosed["total"] = opening_balance data.append(unclosed) if provisional_profit_loss: data.append(provisional_profit_loss) if total_credit: data.append(total_credit) columns = get_columns(filters.periodicity, period_list, company=filters.company) chart = get_chart_data(columns, asset, liability, equity) return columns, data, message, chart
def execute(filters=None): year = filters["Year"] period_list = get_period_list(year, year, "Monthly") columns, data = [], [] columns = get_columns("Monthly", period_list) data = get_data(period_list, filters) return columns, data
def execute(filters=None): period_list = get_period_list(2016, 2016, "Monthly") for period in period_list: pass columns, data = [], [] columns = get_columns() data = get_log_data(filters) chart = get_chart_data(data, period_list) return columns, data, None, chart
def execute(filters=None): columns, data = [], [] if filters.get('fiscal_year'): company = erpnext.get_default_company() period_list = get_period_list(filters.get('fiscal_year'), filters.get('fiscal_year'),"Monthly", company) columns=get_columns() data=get_log_data(filters) chart=get_chart_data(data,period_list) return columns, data, None, chart
def execute(filters=None): period_list = get_period_list(filters.from_fiscal_year, filters.to_fiscal_year, filters.period_start_date, filters.period_end_date, filters.filter_based_on, filters.periodicity, company=filters.company) currency = filters.presentation_currency or frappe.get_cached_value('Company', filters.company, "default_currency") asset = get_data(filters.company, "Asset", "Debit", period_list, only_current_fiscal_year=False, filters=filters, accumulated_values=filters.accumulated_values) liability = get_data(filters.company, "Liability", "Credit", period_list, only_current_fiscal_year=False, filters=filters, accumulated_values=filters.accumulated_values) equity = get_data(filters.company, "Equity", "Credit", period_list, only_current_fiscal_year=False, filters=filters, accumulated_values=filters.accumulated_values) provisional_profit_loss, total_credit = get_provisional_profit_loss(asset, liability, equity, period_list, filters.company, currency) message, opening_balance = check_opening_balance(asset, liability, equity) data = [] data.extend(asset or []) data.extend(liability or []) data.extend(equity or []) if opening_balance and round(opening_balance,2) !=0: unclosed ={ "account_name": "'" + _("Unclosed Fiscal Years Profit / Loss (Credit)") + "'", "account": "'" + _("Unclosed Fiscal Years Profit / Loss (Credit)") + "'", "warn_if_negative": True, "currency": currency } for period in period_list: unclosed[period.key] = opening_balance if provisional_profit_loss: provisional_profit_loss[period.key] = provisional_profit_loss[period.key] - opening_balance unclosed["total"]=opening_balance data.append(unclosed) if provisional_profit_loss: data.append(provisional_profit_loss) if total_credit: data.append(total_credit) columns = get_columns(filters.periodicity, period_list, filters.accumulated_values, company=filters.company) chart = get_chart_data(filters, columns, asset, liability, equity) report_summary = get_report_summary(period_list, asset, liability, equity, provisional_profit_loss, total_credit, currency, filters) return columns, data, message, chart, report_summary
def execute(filters=None): periodicity = filters.get('periodicity') period_list = get_period_list( filters.get('from_fiscal_year'), filters.get('to_fiscal_year'), filters.get('periodicity') ) columns = get_columns(periodicity, period_list) data = get_data(periodicity, period_list, filters) return columns, data
def execute(filters=None): if not filters.periodicity: filters.periodicity = "Monthly" period_list = get_period_list( filters.from_fiscal_year, filters.to_fiscal_year, filters.period_start_date, filters.period_end_date, filters.filter_based_on, filters.periodicity, company=filters.company, ) mappers = get_mappers_from_db() cash_flow_accounts = setup_mappers(mappers) # compute net profit / loss income = get_data( filters.company, "Income", "Credit", period_list, filters=filters, accumulated_values=filters.accumulated_values, ignore_closing_entries=True, ignore_accumulated_values_for_fy=True, ) expense = get_data( filters.company, "Expense", "Debit", period_list, filters=filters, accumulated_values=filters.accumulated_values, ignore_closing_entries=True, ignore_accumulated_values_for_fy=True, ) net_profit_loss = get_net_profit_loss(income, expense, period_list, filters.company) company_currency = frappe.get_cached_value("Company", filters.company, "default_currency") data = compute_data( filters, company_currency, net_profit_loss, period_list, mappers, cash_flow_accounts ) _add_total_row_account(data, data, _("Net Change in Cash"), period_list, company_currency) columns = get_columns( filters.periodicity, period_list, filters.accumulated_values, filters.company ) return columns, data
def execute(filters=None): period_list = get_period_list(filters.from_fiscal_year, filters.to_fiscal_year, filters.periodicity, filters.accumulated_values, filters.company) columns, data = [], [] income = get_data(filters.company, "Income", "Credit", period_list, filters = filters, accumulated_values=filters.accumulated_values, ignore_closing_entries=True, ignore_accumulated_values_for_fy= True, total= False) expense = get_data(filters.company, "Expense", "Debit", period_list, filters=filters, accumulated_values=filters.accumulated_values, ignore_closing_entries=True, ignore_accumulated_values_for_fy= True, total= False) columns = get_columns(filters.periodicity, period_list, filters.accumulated_values, filters.company) gross_income = get_revenue(income, period_list) gross_expense = get_revenue(expense, period_list) if(len(gross_income)==0 and len(gross_expense)== 0): data.append({"account_name": "'" + _("Nothing is included in gross") + "'", "account": "'" + _("Nothing is included in gross") + "'"}) return columns, data data.append({"account_name": "'" + _("Included in Gross Profit") + "'", "account": "'" + _("Included in Gross Profit") + "'"}) data.append({}) data.extend(gross_income or []) data.append({}) data.extend(gross_expense or []) data.append({}) gross_profit = get_profit(gross_income, gross_expense, period_list, filters.company, 'Gross Profit',filters.presentation_currency) data.append(gross_profit) non_gross_income = get_revenue(income, period_list, 0) data.append({}) data.extend(non_gross_income or []) non_gross_expense = get_revenue(expense, period_list, 0) data.append({}) data.extend(non_gross_expense or []) net_profit = get_net_profit(non_gross_income, gross_income, gross_expense, non_gross_expense, period_list, filters.company,filters.presentation_currency) data.append({}) data.append(net_profit) return columns, data
def execute(filters=None): period_list = get_period_list(filters.from_fiscal_year, filters.to_fiscal_year, filters.periodicity, filters.accumulated_values, filters.company) asset = get_data(filters.company, "Asset", "Debit", period_list, only_current_fiscal_year=False, filters=filters, accumulated_values=filters.accumulated_values, ignore_closing_entries=True, ignore_accumulated_values_for_fy=True) liability = get_data(filters.company, "Liability", "Credit", period_list, only_current_fiscal_year=False, filters=filters, accumulated_values=filters.accumulated_values, ignore_closing_entries=True, ignore_accumulated_values_for_fy=True) equity = get_data(filters.company, "Equity", "Credit", period_list, only_current_fiscal_year=False, filters=filters, accumulated_values=filters.accumulated_values, ignore_closing_entries=True, ignore_accumulated_values_for_fy=True) provisional_profit_loss, total_credit = get_provisional_profit_loss(asset, liability, equity, period_list, filters.company) message, opening_balance = check_opening_balance(asset, liability, equity) data = [] data.extend(asset or []) data.extend(liability or []) data.extend(equity or []) if opening_balance and round(opening_balance,2) !=0: unclosed ={ "account_name": "'" + _("Unclosed Fiscal Years Profit / Loss (Credit)") + "'", "account": "'" + _("Unclosed Fiscal Years Profit / Loss (Credit)") + "'", "warn_if_negative": True, "currency": frappe.db.get_value("Company", filters.company, "default_currency") } for period in period_list: unclosed[period.key] = opening_balance if provisional_profit_loss: provisional_profit_loss[period.key] = provisional_profit_loss[period.key] - opening_balance unclosed["total"]=opening_balance data.append(unclosed) if provisional_profit_loss: data.append(provisional_profit_loss) if total_credit: data.append(total_credit) columns = get_columns(filters.periodicity, period_list, filters.accumulated_values, company=filters.company) chart = get_chart_data(filters, columns, asset, liability, equity) return columns, data, message, chart
def get_period_list(self): """ Figure out selected period based on filters """ self.period_list = get_period_list( self.filters.from_fiscal_year, self.filters.to_fiscal_year, self.filters.period_start_date, self.filters.period_end_date, self.filters.filter_based_on, self.filters.periodicity, company=self.filters.company, )
def execute(filters=None): period_list = get_period_list(filters.fiscal_year, filters.periodicity) income = get_data(filters.company, "Income", "Credit", period_list, ignore_closing_entries=True) expense = get_data(filters.company, "Expense", "Debit", period_list, ignore_closing_entries=True) net_profit_loss = get_net_profit_loss(income, expense, period_list) data = [] data.extend(income or []) data.extend(expense or []) if net_profit_loss: data.append(net_profit_loss) columns = get_columns(period_list) return columns, data
def execute(filters=None): period_list = get_period_list(filters.fiscal_year, filters.periodicity, from_beginning=True) asset = get_data(filters.company, "Asset", "Debit", period_list) liability = get_data(filters.company, "Liability", "Credit", period_list) equity = get_data(filters.company, "Equity", "Credit", period_list) provisional_profit_loss = get_provisional_profit_loss(asset, liability, equity, period_list) data = [] data.extend(asset or []) data.extend(liability or []) data.extend(equity or []) if provisional_profit_loss: data.append(provisional_profit_loss) columns = get_columns(period_list) return columns, data
def get_chart_data(data, filters): period_list = get_period_list(filters.fiscal_year, filters.fiscal_year, filters.from_date, filters.to_date, filters.filter_based_on, 'Monthly') fuel_data, service_data = [], [] for period in period_list: total_fuel_exp = 0 total_service_exp = 0 for row in data: if row.date <= period.to_date and row.date >= period.from_date: total_fuel_exp += flt(row.fuel_expense) total_service_exp += flt(row.service_expense) fuel_data.append([period.key, total_fuel_exp]) service_data.append([period.key, total_service_exp]) labels = [period.label for period in period_list] fuel_exp_data = [row[1] for row in fuel_data] service_exp_data = [row[1] for row in service_data] datasets = [] if fuel_exp_data: datasets.append({'name': _('Fuel Expenses'), 'values': fuel_exp_data}) if service_exp_data: datasets.append({ 'name': _('Service Expenses'), 'values': service_exp_data }) chart = { 'data': { 'labels': labels, 'datasets': datasets }, 'type': 'line', 'fieldtype': 'Currency' } return chart
def get_chart_data(data, filters): period_list = get_period_list( filters.fiscal_year, filters.fiscal_year, filters.from_date, filters.to_date, filters.filter_based_on, "Monthly", ) fuel_data, service_data = [], [] for period in period_list: total_fuel_exp = 0 total_service_exp = 0 for row in data: if row.date <= period.to_date and row.date >= period.from_date: total_fuel_exp += flt(row.fuel_expense) total_service_exp += flt(row.service_expense) fuel_data.append([period.key, total_fuel_exp]) service_data.append([period.key, total_service_exp]) labels = [period.label for period in period_list] fuel_exp_data = [row[1] for row in fuel_data] service_exp_data = [row[1] for row in service_data] datasets = [] if fuel_exp_data: datasets.append({"name": _("Fuel Expenses"), "values": fuel_exp_data}) if service_exp_data: datasets.append({"name": _("Service Expenses"), "values": service_exp_data}) chart = { "data": {"labels": labels, "datasets": datasets}, "type": "line", "fieldtype": "Currency", } return chart
def execute(filters=None): period_list = get_period_list(filters.fiscal_year, filters.periodicity) asset = get_data(filters.company, "Asset", "Debit", period_list, only_current_fiscal_year=False) liability = get_data(filters.company, "Liability", "Credit", period_list, only_current_fiscal_year=False) equity = get_data(filters.company, "Equity", "Credit", period_list, only_current_fiscal_year=False) provisional_profit_loss = get_provisional_profit_loss(asset, liability, equity, period_list, filters.company) data = [] data.extend(asset or []) data.extend(liability or []) data.extend(equity or []) if provisional_profit_loss: data.append(provisional_profit_loss) columns = get_columns(filters.periodicity, period_list, company=filters.company) return columns, data
def execute(filters=None): period_list = get_period_list(filters.from_fiscal_year, filters.to_fiscal_year, filters.periodicity, filters.accumulated_values, filters.company) income = get_data(filters.company, "Income", "Credit", period_list, filters=filters, accumulated_values=filters.accumulated_values, ignore_closing_entries=True, ignore_accumulated_values_for_fy=True) expense = get_data(filters.company, "Expense", "Debit", period_list, filters=filters, accumulated_values=filters.accumulated_values, ignore_closing_entries=True, ignore_accumulated_values_for_fy=True) net_profit_loss = get_net_profit_loss(income, expense, period_list, filters.company) cost_of_goods_entries, expense = extract_cost_of_goods_sold(expense) print(expense[0]) data = [] data.extend(income or []) data.extend(cost_of_goods_entries or []) data.extend(expense or []) if net_profit_loss: data.append(net_profit_loss) columns = get_columns(filters.periodicity, period_list, filters.accumulated_values, filters.company) chart = get_chart_data(filters, columns, income, expense, net_profit_loss) return columns, data, None, chart
def execute(filters=None): period_list = get_period_list(filters.fiscal_year, filters.periodicity) asset = get_data(filters.company, "Asset", "Debit", period_list, only_current_fiscal_year=False) liability = get_data(filters.company, "Liability", "Credit", period_list, only_current_fiscal_year=False) equity = get_data(filters.company, "Equity", "Credit", period_list, only_current_fiscal_year=False) provisional_profit_loss,total_credit = get_provisional_profit_loss(asset, liability, equity, period_list, filters.company) opening_balance = flt(asset[0].get("opening_balance", 0)) if liability: opening_balance -= flt(liability[0].get("opening_balance", 0)) if equity: opening_balance -= flt(equity[0].get("opening_balance", 0)) data = [] data.extend(asset or []) data.extend(liability or []) data.extend(equity or []) if round(opening_balance,2) !=0: unclosed ={ "account_name": "'" + _("Unclosed Fiscal Years Profit / Loss (Credit)") + "'", "account": None, "warn_if_negative": True, "currency": frappe.db.get_value("Company", filters.company, "default_currency") } for period in period_list: unclosed[period.key] = opening_balance provisional_profit_loss[period.key] = provisional_profit_loss[period.key] - opening_balance unclosed["total"]=opening_balance data.append(unclosed) if provisional_profit_loss: data.append(provisional_profit_loss) data.append(total_credit) columns = get_columns(filters.periodicity, period_list, company=filters.company) return columns, data
def simulate_future_posting(self): """ simulate future posting by creating dummy gl entries. starts from the last posting date. """ if self.service_start_date != self.service_end_date: if add_days(self.last_entry_date, 1) < self.period_list[-1].to_date: self.estimate_for_period_list = get_period_list( self.filters.from_fiscal_year, self.filters.to_fiscal_year, add_days(self.last_entry_date, 1), self.period_list[-1].to_date, "Date Range", "Monthly", company=self.filters.company, ) for period in self.estimate_for_period_list: amount = self.calculate_amount(period.from_date, period.to_date) gle = self.make_dummy_gle(period.key, period.to_date, amount) self.gle_entries.append(gle)
def execute(filters=None): period_list = get_period_list(filters.from_fiscal_year, filters.to_fiscal_year, filters.periodicity, filters.company) operation_accounts = { "section_name": "Operations", "section_footer": _("Net Cash from Operations"), "section_header": _("Cash Flow from Operations"), "account_types": [{ "account_type": "Depreciation", "label": _("Depreciation") }, { "account_type": "Receivable", "label": _("Net Change in Accounts Receivable") }, { "account_type": "Payable", "label": _("Net Change in Accounts Payable") }, { "account_type": "Stock", "label": _("Net Change in Inventory") }] } investing_accounts = { "section_name": "Investing", "section_footer": _("Net Cash from Investing"), "section_header": _("Cash Flow from Investing"), "account_types": [{ "account_type": "Fixed Asset", "label": _("Net Change in Fixed Asset") }] } financing_accounts = { "section_name": "Financing", "section_footer": _("Net Cash from Financing"), "section_header": _("Cash Flow from Financing"), "account_types": [{ "account_type": "Equity", "label": _("Net Change in Equity") }] } # combine all cash flow accounts for iteration cash_flow_accounts = [] cash_flow_accounts.append(operation_accounts) cash_flow_accounts.append(investing_accounts) cash_flow_accounts.append(financing_accounts) # compute net profit / loss income = get_data(filters.company, "Income", "Credit", period_list, accumulated_values=filters.accumulated_values, ignore_closing_entries=True, ignore_accumulated_values_for_fy=True) expense = get_data(filters.company, "Expense", "Debit", period_list, accumulated_values=filters.accumulated_values, ignore_closing_entries=True, ignore_accumulated_values_for_fy=True) net_profit_loss = get_net_profit_loss(income, expense, period_list, filters.company) data = [] company_currency = frappe.db.get_value("Company", filters.company, "default_currency") for cash_flow_account in cash_flow_accounts: section_data = [] data.append({ "account_name": cash_flow_account['section_header'], "parent_account": None, "indent": 0.0, "account": cash_flow_account['section_header'] }) if len(data) == 1: # add first net income in operations section if net_profit_loss: net_profit_loss.update({ "indent": 1, "parent_account": operation_accounts['section_header'] }) data.append(net_profit_loss) section_data.append(net_profit_loss) for account in cash_flow_account['account_types']: account_data = get_account_type_based_data( filters.company, account['account_type'], period_list, filters.accumulated_values) account_data.update({ "account_name": account['label'], "account": account['label'], "indent": 1, "parent_account": cash_flow_account['section_header'], "currency": company_currency }) data.append(account_data) section_data.append(account_data) add_total_row_account(data, section_data, cash_flow_account['section_footer'], period_list, company_currency) add_total_row_account(data, data, _("Net Change in Cash"), period_list, company_currency) columns = get_columns(filters.periodicity, period_list, filters.accumulated_values, filters.company) return columns, data
def execute(filters=None): period_list = get_period_list(filters.from_date, filters.to_date, filters.periodicity, company=filters.company) currency = filters.presentation_currency or frappe.get_cached_value('Company', filters.company, "default_currency") asset = get_data(filters.company, "Asset", "Debit", period_list, only_current_fiscal_year=False, filters=filters, accumulated_values=filters.accumulated_values) liability = get_data(filters.company, "Liability", "Credit", period_list, only_current_fiscal_year=False, filters=filters, accumulated_values=filters.accumulated_values) equity = get_data(filters.company, "Equity", "Credit", period_list, only_current_fiscal_year=False, filters=filters, accumulated_values=filters.accumulated_values) provisional_profit_loss, total_credit = get_provisional_profit_loss(asset, liability, equity, period_list, filters.company, currency) message, opening_balance = check_opening_balance(asset, liability, equity) data = [] #add assets to the balance sheet report data.extend(asset or []) #add liabilities to the balance sheet report data.extend(liability or []) #add provisional profit/loss and adjust equity totals if books are not closed if len(equity) > 2 and provisional_profit_loss: equity = append_provisions_to_equity(equity, provisional_profit_loss, period_list) data.extend(equity or []) #specific case when equity is completely empty but we still need to add elif not equity and provisional_profit_loss: equity = create_equity_with_provisions(provisional_profit_loss, period_list) data.extend(equity) #add equity to the balance sheet report if there are no provisional profits/losses else: data.extend(equity or []) if opening_balance and round(opening_balance,2) !=0: unclosed ={ "account_name": "'" + _("Unclosed Fiscal Years Profit / Loss (Credit)") + "'", "account": "'" + _("Unclosed Fiscal Years Profit / Loss (Credit)") + "'", "warn_if_negative": True, "currency": currency } for period in period_list: unclosed[period.key] = opening_balance if provisional_profit_loss: provisional_profit_loss[period.key] = provisional_profit_loss[period.key] - opening_balance unclosed["total"]=opening_balance data.append(unclosed) if total_credit: data.append({}) data.append(total_credit) columns = get_columns(filters.periodicity, period_list, filters.accumulated_values, company=filters.company) chart = get_chart_data(filters, columns, asset, liability, equity) return columns, data, message, chart
def execute(filters=None): period_list = get_period_list(filters.from_fiscal_year, filters.to_fiscal_year, filters.period_start_date, filters.period_end_date, filters.filter_based_on, filters.periodicity, filters.accumulated_values, filters.company) columns, data = [], [] income = get_data(filters.company, "Income", "Credit", period_list, filters=filters, accumulated_values=filters.accumulated_values, ignore_closing_entries=True, ignore_accumulated_values_for_fy=True, total=False) expense = get_data(filters.company, "Expense", "Debit", period_list, filters=filters, accumulated_values=filters.accumulated_values, ignore_closing_entries=True, ignore_accumulated_values_for_fy=True, total=False) columns = get_columns(filters.periodicity, period_list, filters.accumulated_values, filters.company) gross_income = get_revenue(income, period_list) gross_expense = get_revenue(expense, period_list) if (len(gross_income) == 0 and len(gross_expense) == 0): data.append({ "account_name": "'" + _("Nothing is included in gross") + "'", "account": "'" + _("Nothing is included in gross") + "'" }) return columns, data # to avoid error eg: gross_income[0] : list index out of range if not gross_income: gross_income = [{}] if not gross_expense: gross_expense = [{}] data.append({ "account_name": "'" + _("Included in Gross Profit") + "'", "account": "'" + _("Included in Gross Profit") + "'" }) data.append({}) data.extend(gross_income or []) data.append({}) data.extend(gross_expense or []) data.append({}) gross_profit = get_profit(gross_income, gross_expense, period_list, filters.company, 'Gross Profit', filters.presentation_currency) data.append(gross_profit) non_gross_income = get_revenue(income, period_list, 0) data.append({}) data.extend(non_gross_income or []) non_gross_expense = get_revenue(expense, period_list, 0) data.append({}) data.extend(non_gross_expense or []) net_profit = get_net_profit(non_gross_income, gross_income, gross_expense, non_gross_expense, period_list, filters.company, filters.presentation_currency) data.append({}) data.append(net_profit) return columns, data
def execute(filters=None): def get_accounts_matched_account_number(company, accounts): accounts_codes = str(accounts).split(" ") account_matches = dict(debit=[], credit=[]) for account_match in accounts_codes: if account_match.startswith("+"): account_matches.get('debit').extend( get_accounts_with_account_number(company, account_match[1:])) elif account_match.startswith("-"): account_matches.get('credit').extend( get_accounts_with_account_number(company, account_match[1:])) return account_matches def get_accounts_with_account_number(company, match): accounts = frappe.db.sql(""" select name from `tabAccount` where company=%s and is_group = 0 and account_number LIKE %s order by lft""", (company, match + "%"), as_dict=True) return accounts def remove_group_accounts(grouper): grouper = { key: value for (key, value) in grouper.items() if value.is_group == 0 } def set_parent_value(group, account_): for account in itervalues(group): if account.get('value') is None: values = account_.get('value') keys = account_.get('keys') account['value'] = values account['keys'] = keys # account[keys[0]] = values[0] # account[keys[1]] = values[1] else: break def compute_from_grouper_old(grouper, accounts, period_list): value_current_year = 0 value_previous_year = 0 for account in accounts.get("debit"): acc = grouper.get(account.get("name")) if acc is not None: value_previous_year += acc.get( "%s_debit" % period_list[0].get('key'), 0.0) value_current_year += acc.get( "%s_debit" % period_list[1].get('key'), 0.0) for account in accounts.get("credit"): acc = grouper.get(account.get("name")) if acc is not None: value_previous_year += acc.get( "%s_credit" % period_list[0].get('key'), 0.0) value_current_year += acc.get( "%s_credit" % period_list[1].get('key'), 0.0) return (value_previous_year, value_current_year) def compute_from_grouper(grouper, accounts, period_list): value_current_year = 0 value_previous_year = 0 previous_key = period_list[0].get('key') current_key = period_list[1].get('key') for account in accounts.get("debit"): acc = grouper.get(account.get("name")) if acc is not None: value_previous_year_d = acc.get("%s_debit" % previous_key, 0.0) value_previous_year_c = acc.get("%s_credit" % previous_key, 0.0) value_current_year_d = acc.get("%s_debit" % current_key, 0.0) value_current_year_c = acc.get("%s_credit" % current_key, 0.0) diff_value_previous_year = value_previous_year_d - value_previous_year_c diff_value_current_year = value_current_year_d - value_current_year_c if diff_value_previous_year >= 0: value_previous_year += diff_value_previous_year if diff_value_current_year >= 0: value_current_year += diff_value_current_year for account in accounts.get("credit"): acc = grouper.get(account.get("name")) if acc is not None: value_previous_year_d = acc.get("%s_debit" % previous_key, 0.0) value_previous_year_c = acc.get("%s_credit" % previous_key, 0.0) value_current_year_d = acc.get("%s_debit" % current_key, 0.0) value_current_year_c = acc.get("%s_credit" % current_key, 0.0) diff_value_previous_year = value_previous_year_d - value_previous_year_c diff_value_current_year = value_current_year_d - value_current_year_c if diff_value_previous_year < 0: value_previous_year += diff_value_previous_year if diff_value_current_year < 0: value_current_year += diff_value_current_year return (value_previous_year, value_current_year, previous_key, current_key) period_list = get_period_list(int(filters.to_fiscal_year) - 1, filters.to_fiscal_year, filters.periodicity, company=filters.company) currency = filters.presentation_currency or frappe.get_cached_value( 'Company', filters.company, "default_currency") if period_list: current_year_from_date = period_list[len(period_list) - 1] current_year_from_date.from_date = datetime.strptime( filters.period_start_date, '%Y-%m-%d').date() current_year_from_date.to_date = datetime.strptime( filters.period_end_date, '%Y-%m-%d').date() period_list[len(period_list) - 1] = current_year_from_date grouper = {} asset = get_data(filters.company, "Asset", "Debit", period_list, grouper, only_current_fiscal_year=False, filters=filters, accumulated_values=filters.accumulated_values) liability = get_data(filters.company, "Liability", "Credit", period_list, grouper, only_current_fiscal_year=False, filters=filters, accumulated_values=filters.accumulated_values) equity = get_data(filters.company, "Equity", "Credit", period_list, grouper, only_current_fiscal_year=False, filters=filters, accumulated_values=filters.accumulated_values) # grouper now hold leaf accounts that have credit and debit balance for current date and last fiscal year # at a little cost # Asset get debit balance of all specified accounts # Liability get the credit balance of all specified accounts remove_group_accounts(grouper) configuration = frappe.get_doc('Financial Report Configuration', {"report_code": "Bilan_TN"}) grouping, group = dict(), None indentation = { "H1": 0.0, "H2": 1.0, "H3": 2.0, "H0": 2.0, "H4": 3.0, "H5": 4.0, "H6": 5.0, } for (index, config) in enumerate( configuration.get("financial_report_configuration_item")): group = config.get( 'label') if config.get('type') == 'H1' or group is None else group if config.get('type') == 'H1': grouping[group] = {} parent = config.get('label') index_plus = index + 1 if config.get('accounts') is None: account_ = dict(account=config.get('label'), tag=config.get('type'), title=config.get('label'), index=indentation.get(config.get('type')), indent=indentation.get(config.get('type')), parent="", is_group=1) grouping[group].update({"L%s" % index_plus: account_}) elif str(config.get('accounts')).find("L") is not -1: total_current_year = total_previous_year = 0 key_prev = key_current = "" codes = str(config.get('accounts')).split(" ") for code in codes: index_ = int(str(code[2:])) account_at_index = grouping[group].get("L%s" % index_) if account_at_index and account_at_index.get('value'): total_previous_year = total_previous_year + account_at_index.get( 'value')[0] total_current_year = total_current_year + account_at_index.get( 'value')[1] key_prev = account_at_index.get('keys')[0] key_current = account_at_index.get('keys')[1] account_ = dict(account=config.get('label'), tag=config.get('type'), title=config.get('label'), value=(total_previous_year, total_current_year), indent=indentation.get(config.get('type')), index=indentation.get(config.get('type')), keys=(key_prev, key_current), parent=parent, is_group=0) account_[key_prev] = total_previous_year account_[key_current] = total_current_year grouping[group].update({"L%s" % index_plus: account_}) set_parent_value(grouping[group], account_) else: prev, current, key1, key2 = compute_from_grouper( grouper, get_accounts_matched_account_number(filters.company, config.get('accounts')), period_list) account_ = dict(account=config.get('label'), tag=config.get('type'), title=config.get('label'), index=indentation.get(config.get('type')), indent=indentation.get(config.get('type')), value=(prev, current), parent=parent, keys=(key1, key2), is_group=0) account_[key1] = prev account_[key2] = current grouping[group].update({"L%s" % index_plus: account_}) columns = get_columns(filters.periodicity, period_list, filters.accumulated_values, company=filters.company) data = [] for a in [list(itervalues(iter)) for iter in itervalues(grouping)]: data.extend(a) return columns, data, None, None, None
def execute(filters=None): period_list = get_period_list(filters.from_fiscal_year, filters.to_fiscal_year, filters.periodicity, filters.company) operation_accounts = { "section_name": "Operations", "section_footer": _("Net Cash from Operations"), "section_header": _("Cash Flow from Operations"), "account_types": [ {"account_type": "Depreciation", "label": _("Depreciation")}, {"account_type": "Receivable", "label": _("Net Change in Accounts Receivable")}, {"account_type": "Payable", "label": _("Net Change in Accounts Payable")}, {"account_type": "Stock", "label": _("Net Change in Inventory")} ] } investing_accounts = { "section_name": "Investing", "section_footer": _("Net Cash from Investing"), "section_header": _("Cash Flow from Investing"), "account_types": [ {"account_type": "Fixed Asset", "label": _("Net Change in Fixed Asset")} ] } financing_accounts = { "section_name": "Financing", "section_footer": _("Net Cash from Financing"), "section_header": _("Cash Flow from Financing"), "account_types": [ {"account_type": "Equity", "label": _("Net Change in Equity")} ] } # combine all cash flow accounts for iteration cash_flow_accounts = [] cash_flow_accounts.append(operation_accounts) cash_flow_accounts.append(investing_accounts) cash_flow_accounts.append(financing_accounts) # compute net profit / loss income = get_data(filters.company, "Income", "Credit", period_list, accumulated_values=filters.accumulated_values, ignore_closing_entries=True, ignore_accumulated_values_for_fy= True) expense = get_data(filters.company, "Expense", "Debit", period_list, accumulated_values=filters.accumulated_values, ignore_closing_entries=True, ignore_accumulated_values_for_fy= True) net_profit_loss = get_net_profit_loss(income, expense, period_list, filters.company) data = [] company_currency = frappe.db.get_value("Company", filters.company, "default_currency") for cash_flow_account in cash_flow_accounts: section_data = [] data.append({ "account_name": cash_flow_account['section_header'], "parent_account": None, "indent": 0.0, "account": cash_flow_account['section_header'] }) if len(data) == 1: # add first net income in operations section if net_profit_loss: net_profit_loss.update({ "indent": 1, "parent_account": operation_accounts['section_header'] }) data.append(net_profit_loss) section_data.append(net_profit_loss) for account in cash_flow_account['account_types']: account_data = get_account_type_based_data(filters.company, account['account_type'], period_list, filters.accumulated_values) account_data.update({ "account_name": account['label'], "account": account['label'], "indent": 1, "parent_account": cash_flow_account['section_header'], "currency": company_currency }) data.append(account_data) section_data.append(account_data) add_total_row_account(data, section_data, cash_flow_account['section_footer'], period_list, company_currency) add_total_row_account(data, data, _("Net Change in Cash"), period_list, company_currency) columns = get_columns(filters.periodicity, period_list, filters.accumulated_values, filters.company) return columns, data
def execute(filters=None): sales_accounts_names = get_sales_accounts_names(filters) period_list = get_period_list(filters.from_fiscal_year, filters.to_fiscal_year, filters.period_start_date, filters.period_end_date, filters.filter_based_on, filters.periodicity, company=filters.company) income = get_data(filters.company, "Income", "Credit", period_list, filters = filters, accumulated_values=filters.accumulated_values, ignore_closing_entries=True, ignore_accumulated_values_for_fy= True) expense = get_data(filters.company, "Expense", "Debit", period_list, filters=filters, accumulated_values=filters.accumulated_values, ignore_closing_entries=True, ignore_accumulated_values_for_fy= True) liability = get_data(filters.company, "Liability", "Credit", period_list, only_current_fiscal_year=False, filters=filters, accumulated_values=filters.accumulated_values) assets = get_data(filters.company, "Asset", "Debit", period_list, only_current_fiscal_year=False, filters=filters, accumulated_values=filters.accumulated_values) assets_ = None for i in range(len(assets) - 1, 0, -1): if assets[i] and assets[i].get('account') == 'Total Asset (Debit)': assets_ = assets[i] break equity = get_data(filters.company, "Equity", "Credit", period_list, only_current_fiscal_year=False, filters=filters, accumulated_values=filters.accumulated_values) for i in range(len(equity) - 1, 0, -1): if equity[i] and equity[i].get('account') == 'Total Equity (Credit)': equity = equity[i] break net_profit_loss = get_net_profit_loss(income, expense, period_list, filters.company, filters.presentation_currency) # print(equity) data = [] p_and_l_data = [] p_and_l_data.extend(income or []) p_and_l_data.extend(expense or []) if net_profit_loss: p_and_l_data.append(net_profit_loss) data.extend(get_net_profit_margin(p_and_l_data, sales_accounts_names, period_list)) data.extend(get_return_on_assets(net_profit_loss, assets_, period_list)) data.extend(get_return_on_equity(net_profit_loss, equity, period_list)) data.extend(get_current_ratio(assets, liability, period_list)) # print(equity) # print('==================================================') # print(p_and_l_data) # print(data) columns = get_columns(filters.periodicity, period_list, filters.accumulated_values, filters.company) # print(columns) return columns, data