def validate_filters(filters): ''' Validate if dates are properly set and lie in the same fiscal year''' if filters.from_date > filters.to_date: frappe.throw(_("From Date must be before To Date")) from_year = get_fiscal_year(filters.from_date)[0] to_year = get_fiscal_year(filters.to_date)[0] if from_year != to_year: frappe.throw(_("From Date and To Date lie in different Fiscal Year")) filters["fiscal_year"] = from_year
def get_period_date_ranges(self): from dateutil.relativedelta import relativedelta, MO from_date, to_date = getdate(self.filters.from_date), getdate(self.filters.to_date) increment = { "Monthly": 1, "Quarterly": 3, "Half-Yearly": 6, "Yearly": 12 }.get(self.filters.range, 1) if self.filters.range in ['Monthly', 'Quarterly']: from_date = from_date.replace(day=1) elif self.filters.range == "Yearly": from_date = get_fiscal_year(from_date)[1] else: from_date = from_date + relativedelta(from_date, weekday=MO(-1)) self.periodic_daterange = [] for dummy in range(1, 53): if self.filters.range == "Weekly": period_end_date = add_days(from_date, 6) else: period_end_date = add_to_date(from_date, months=increment, days=-1) if period_end_date > to_date: period_end_date = to_date self.periodic_daterange.append(period_end_date) from_date = add_days(period_end_date, 1) if period_end_date == to_date: break
def get_party_tax_withholding_details(ref_doc, tax_withholding_category=None): pan_no = '' suppliers = [] if not tax_withholding_category: tax_withholding_category, pan_no = frappe.db.get_value('Supplier', ref_doc.supplier, ['tax_withholding_category', 'pan']) if not tax_withholding_category: return if not pan_no: pan_no = frappe.db.get_value('Supplier', ref_doc.supplier, 'pan') # Get others suppliers with the same PAN No if pan_no: suppliers = [d.name for d in frappe.get_all('Supplier', fields=['name'], filters={'pan': pan_no})] if not suppliers: suppliers.append(ref_doc.supplier) fy = get_fiscal_year(ref_doc.posting_date, company=ref_doc.company) tax_details = get_tax_withholding_details(tax_withholding_category, fy[0], ref_doc.company) if not tax_details: frappe.throw(_('Please set associated account in Tax Withholding Category {0} against Company {1}') .format(tax_withholding_category, ref_doc.company)) tds_amount = get_tds_amount(suppliers, ref_doc.net_total, ref_doc.company, tax_details, fy, ref_doc.posting_date, pan_no) tax_row = get_tax_row(tax_details, tds_amount) return tax_row
def get_start_end_dates(payroll_frequency, start_date=None, company=None): '''Returns dict of start and end dates for given payroll frequency based on start_date''' if payroll_frequency == "Monthly" or payroll_frequency == "Bimonthly" or payroll_frequency == "": fiscal_year = get_fiscal_year(start_date, company=company)[0] month = "%02d" % getdate(start_date).month m = get_month_details(fiscal_year, month) if payroll_frequency == "Bimonthly": if getdate(start_date).day <= 15: start_date = m['month_start_date'] end_date = m['month_mid_end_date'] else: start_date = m['month_mid_start_date'] end_date = m['month_end_date'] else: start_date = m['month_start_date'] end_date = m['month_end_date'] if payroll_frequency == "Weekly": end_date = add_days(start_date, 6) if payroll_frequency == "Fortnightly": end_date = add_days(start_date, 13) if payroll_frequency == "Daily": end_date = start_date return frappe._dict({'start_date': start_date, 'end_date': end_date})
def test_payroll_frequency(self): fiscal_year = get_fiscal_year(nowdate(), company=erpbee.get_default_company())[0] month = "%02d" % getdate(nowdate()).month m = get_month_details(fiscal_year, month) for payroll_frequency in [ "Monthly", "Bimonthly", "Fortnightly", "Weekly", "Daily" ]: make_employee(payroll_frequency + "*****@*****.**") ss = make_employee_salary_slip( payroll_frequency + "*****@*****.**", payroll_frequency, payroll_frequency + "_Test Payroll Frequency") if payroll_frequency == "Monthly": self.assertEqual(ss.end_date, m['month_end_date']) elif payroll_frequency == "Bimonthly": if getdate(ss.start_date).day <= 15: self.assertEqual(ss.end_date, m['month_mid_end_date']) else: self.assertEqual(ss.end_date, m['month_end_date']) elif payroll_frequency == "Fortnightly": self.assertEqual(ss.end_date, add_days(nowdate(), 13)) elif payroll_frequency == "Weekly": self.assertEqual(ss.end_date, add_days(nowdate(), 6)) elif payroll_frequency == "Daily": self.assertEqual(ss.end_date, nowdate())
def test_monthly_budget_crossed_for_mr(self): budget = make_budget(applicable_on_material_request=1, applicable_on_purchase_order=1, action_if_accumulated_monthly_budget_exceeded_on_mr="Stop", budget_against="Cost Center") fiscal_year = get_fiscal_year(nowdate())[0] frappe.db.set_value("Budget", budget.name, "action_if_accumulated_monthly_budget_exceeded", "Stop") frappe.db.set_value("Budget", budget.name, "fiscal_year", fiscal_year) mr = frappe.get_doc({ "doctype": "Material Request", "material_request_type": "Purchase", "transaction_date": nowdate(), "company": budget.company, "items": [{ 'item_code': '_Test Item', 'qty': 1, 'uom': "_Test UOM", 'warehouse': '_Test Warehouse - _TC', 'schedule_date': nowdate(), 'rate': 100000, 'expense_account': '_Test Account Cost for Goods Sold - _TC', 'cost_center': '_Test Cost Center - _TC' }] }) mr.set_missing_values() self.assertRaises(BudgetError, mr.submit) budget.load_from_db() budget.cancel()
def set_total_expense_zero(posting_date, budget_against_field=None, budget_against_CC=None): if budget_against_field == "project": budget_against = "_Test Project" else: budget_against = budget_against_CC or "_Test Cost Center - _TC" fiscal_year = get_fiscal_year(nowdate())[0] args = frappe._dict({ "account": "_Test Account Cost for Goods Sold - _TC", "cost_center": "_Test Cost Center - _TC", "monthly_end_date": posting_date, "company": "_Test Company", "fiscal_year": fiscal_year, "budget_against_field": budget_against_field, }) if not args.get(budget_against_field): args[budget_against_field] = budget_against existing_expense = get_actual_expense(args) if existing_expense: if budget_against_field == "cost_center": make_journal_entry("_Test Account Cost for Goods Sold - _TC", "_Test Bank - _TC", -existing_expense, "_Test Cost Center - _TC", posting_date=nowdate(), submit=True) elif budget_against_field == "project": make_journal_entry("_Test Account Cost for Goods Sold - _TC", "_Test Bank - _TC", -existing_expense, "_Test Cost Center - _TC", submit=True, project="_Test Project", posting_date=nowdate())
def make_holiday(holiday_list_name): if not frappe.db.exists('Holiday List', holiday_list_name): current_fiscal_year = get_fiscal_year(nowdate(), as_dict=True) dt = getdate(nowdate()) new_year = dt + relativedelta(month=1, day=1, year=dt.year) republic_day = dt + relativedelta(month=1, day=26, year=dt.year) test_holiday = dt + relativedelta(month=2, day=2, year=dt.year) frappe.get_doc({ 'doctype': 'Holiday List', 'from_date': current_fiscal_year.year_start_date, 'to_date': current_fiscal_year.year_end_date, 'holiday_list_name': holiday_list_name, 'holidays': [{ 'holiday_date': new_year, 'description': 'New Year' }, { 'holiday_date': republic_day, 'description': 'Republic Day' }, { 'holiday_date': test_holiday, 'description': 'Test Holiday' }] }).insert() return holiday_list_name
def set_tax_withholding_category(company): accounts = [] abbr = frappe.get_value("Company", company, "abbr") tds_account = frappe.get_value("Account", 'TDS Payable - {0}'.format(abbr), 'name') if company and tds_account: accounts = [dict(company=company, account=tds_account)] fiscal_year = get_fiscal_year(today(), company=company)[0] docs = get_tds_details(accounts, fiscal_year) for d in docs: try: doc = frappe.get_doc(d) doc.flags.ignore_permissions = True doc.flags.ignore_mandatory = True doc.insert() except frappe.DuplicateEntryError: doc = frappe.get_doc("Tax Withholding Category", d.get("name")) if accounts: doc.append("accounts", accounts[0]) # if fiscal year don't match with any of the already entered data, append rate row fy_exist = [ k for k in doc.get('rates') if k.get('fiscal_year') == fiscal_year ] if not fy_exist: doc.append("rates", d.get('rates')[0]) doc.save()
def validate_and_set_fiscal_year(self): if not self.fiscal_year: self.fiscal_year = get_fiscal_year(self.posting_date, company=self.company)[0] else: from erpbee.accounts.utils import validate_fiscal_year validate_fiscal_year(self.posting_date, self.fiscal_year, self.company, self.meta.get_label("posting_date"), self)
def get_start_date(period, accumulated_values, company): if not accumulated_values and period.get('from_date'): return period['from_date'] start_date = period["year_start_date"] if accumulated_values: start_date = get_fiscal_year(period.to_date, company=company)[1] return start_date
def make_budget(**args): args = frappe._dict(args) budget_against=args.budget_against cost_center=args.cost_center fiscal_year = get_fiscal_year(nowdate())[0] if budget_against == "Project": project_name = "{0}%".format("_Test Project/" + fiscal_year) budget_list = frappe.get_all("Budget", fields=["name"], filters = {"name": ("like", project_name)}) else: cost_center_name = "{0}%".format(cost_center or "_Test Cost Center - _TC/" + fiscal_year) budget_list = frappe.get_all("Budget", fields=["name"], filters = {"name": ("like", cost_center_name)}) for d in budget_list: frappe.db.sql("delete from `tabBudget` where name = %(name)s", d) frappe.db.sql("delete from `tabBudget Account` where parent = %(name)s", d) budget = frappe.new_doc("Budget") if budget_against == "Project": budget.project = "_Test Project" else: budget.cost_center =cost_center or "_Test Cost Center - _TC" monthly_distribution = frappe.get_doc("Monthly Distribution", "_Test Distribution") monthly_distribution.fiscal_year = fiscal_year budget.fiscal_year = fiscal_year budget.monthly_distribution = "_Test Distribution" budget.company = "_Test Company" budget.applicable_on_booking_actual_expenses = 1 budget.action_if_annual_budget_exceeded = "Stop" budget.action_if_accumulated_monthly_budget_exceeded = "Ignore" budget.budget_against = budget_against budget.append("accounts", { "account": "_Test Account Cost for Goods Sold - _TC", "budget_amount": 200000 }) if args.applicable_on_material_request: budget.applicable_on_material_request = 1 budget.action_if_annual_budget_exceeded_on_mr = args.action_if_annual_budget_exceeded_on_mr or 'Warn' budget.action_if_accumulated_monthly_budget_exceeded_on_mr = args.action_if_accumulated_monthly_budget_exceeded_on_mr or 'Warn' if args.applicable_on_purchase_order: budget.applicable_on_purchase_order = 1 budget.action_if_annual_budget_exceeded_on_po = args.action_if_annual_budget_exceeded_on_po or 'Warn' budget.action_if_accumulated_monthly_budget_exceeded_on_po = args.action_if_accumulated_monthly_budget_exceeded_on_po or 'Warn' budget.insert() budget.submit() return budget
def get_period(self, posting_date): if self.filters.range == 'Weekly': period = "Week " + str(posting_date.isocalendar()[1]) + " " + str(posting_date.year) elif self.filters.range == 'Monthly': period = str(self.months[posting_date.month - 1]) + " " + str(posting_date.year) elif self.filters.range == 'Quarterly': period = "Quarter " + str(((posting_date.month - 1) // 3) + 1) + " " + str(posting_date.year) else: year = get_fiscal_year(posting_date, company=self.filters.company) period = str(year[0]) return period
def download_datev_csv(filters): """ Provide accounting entries for download in DATEV format. Validate the filters, get the data, produce the CSV file and provide it for download. Can be called like this: GET /api/method/erpbee.regional.report.datev.datev.download_datev_csv Arguments / Params: filters -- dict of filters to be passed to the sql query """ if isinstance(filters, string_types): filters = json.loads(filters) validate(filters) company = filters.get('company') fiscal_year = get_fiscal_year(date=filters.get('from_date'), company=company) filters['fiscal_year_start'] = fiscal_year[1] # set chart of accounts used coa = frappe.get_value('Company', company, 'chart_of_accounts') filters['skr'] = '04' if 'SKR04' in coa else ('03' if 'SKR03' in coa else '') filters['account_number_length'] = frappe.get_value('DATEV Settings', company, 'account_number_length') transactions = get_transactions(filters) account_names = get_account_names(filters) customers = get_customers(filters) suppliers = get_suppliers(filters) zip_name = '{} DATEV.zip'.format(frappe.utils.datetime.date.today()) zip_and_download(zip_name, [ { 'file_name': 'EXTF_Buchungsstapel.csv', 'csv_data': get_datev_csv(transactions, filters, csv_class=Transactions) }, { 'file_name': 'EXTF_Kontenbeschriftungen.csv', 'csv_data': get_datev_csv(account_names, filters, csv_class=AccountNames) }, { 'file_name': 'EXTF_Kunden.csv', 'csv_data': get_datev_csv(customers, filters, csv_class=DebtorsCreditors) }, { 'file_name': 'EXTF_Lieferanten.csv', 'csv_data': get_datev_csv(suppliers, filters, csv_class=DebtorsCreditors) }, ])
def get_period(posting_date, filters): months = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"] if filters.range == 'Weekly': period = "Week " + str(posting_date.isocalendar()[1]) + " " + str(posting_date.year) elif filters.range == 'Monthly': period = str(months[posting_date.month - 1]) + " " + str(posting_date.year) elif filters.range == 'Quarterly': period = "Quarter " + str(((posting_date.month-1)//3)+1) +" " + str(posting_date.year) else: year = get_fiscal_year(posting_date, company=filters.company) period = str(year[2]) return period
def make_holiday_list(): fiscal_year = get_fiscal_year(nowdate(), company=erpbee.get_default_company()) if not frappe.db.get_value("Holiday List", "Salary Slip Test Holiday List"): holiday_list = frappe.get_doc({ "doctype": "Holiday List", "holiday_list_name": "Salary Slip Test Holiday List", "from_date": fiscal_year[1], "to_date": fiscal_year[2], "weekly_off": "Sunday" }).insert() holiday_list.get_weekly_off_dates() holiday_list.save()
def get_count_for_period(account, fieldname, from_date, to_date): count = 0.0 count_on_to_date = get_count_on(account, fieldname, to_date) count_before_from_date = get_count_on(account, fieldname, from_date - timedelta(days=1)) fy_start_date = get_fiscal_year(to_date)[1] if from_date == fy_start_date: count = count_on_to_date elif from_date > fy_start_date: count = count_on_to_date - count_before_from_date else: last_year_closing_count = get_count_on(account, fieldname, fy_start_date - timedelta(days=1)) count = count_on_to_date + (last_year_closing_count - count_before_from_date) return count
def create_tax_with_holding_category(): fiscal_year = get_fiscal_year(today(), company="_Test Company")[0] # Cummulative thresold if not frappe.db.exists("Tax Withholding Category", "Cumulative Threshold TDS"): frappe.get_doc({ "doctype": "Tax Withholding Category", "name": "Cumulative Threshold TDS", "category_name": "10% TDS", "rates": [{ 'fiscal_year': fiscal_year, 'tax_withholding_rate': 10, 'single_threshold': 0, 'cumulative_threshold': 30000.00 }], "accounts": [{ 'company': '_Test Company', 'account': 'TDS - _TC' }] }).insert() # Single thresold if not frappe.db.exists("Tax Withholding Category", "Single Threshold TDS"): frappe.get_doc({ "doctype": "Tax Withholding Category", "name": "Single Threshold TDS", "category_name": "10% TDS", "rates": [{ 'fiscal_year': fiscal_year, 'tax_withholding_rate': 10, 'single_threshold': 20000.00, 'cumulative_threshold': 0 }], "accounts": [{ 'company': '_Test Company', 'account': 'TDS - _TC' }] }).insert()
def get_period(self, appointment_date): if self.filters.range == 'Weekly': period = 'Week ' + str(appointment_date.isocalendar()[1]) elif self.filters.range == 'Monthly': period = str(self.months[appointment_date.month - 1]) elif self.filters.range == 'Quarterly': period = 'Quarter ' + str(((appointment_date.month - 1) // 3) + 1) else: year = get_fiscal_year(appointment_date, company=self.filters.company) period = str(year[0]) if getdate(self.filters.from_date).year != getdate( self.filters.to_date).year: period += ' ' + str(appointment_date.year) return period
def test_monthly_budget_crossed_for_po(self): budget = make_budget(applicable_on_purchase_order=1, action_if_accumulated_monthly_budget_exceeded_on_po="Stop", budget_against="Cost Center") fiscal_year = get_fiscal_year(nowdate())[0] frappe.db.set_value("Budget", budget.name, "action_if_accumulated_monthly_budget_exceeded", "Stop") frappe.db.set_value("Budget", budget.name, "fiscal_year", fiscal_year) po = create_purchase_order(transaction_date=nowdate(), do_not_submit=True) po.set_missing_values() self.assertRaises(BudgetError, po.submit) budget.load_from_db() budget.cancel() po.cancel()
def validate(self): if getdate(self.valid_upto) < getdate(self.valid_from): frappe.throw(_("Valid Upto date cannot be before Valid From date")) fiscal_year = get_fiscal_year(fiscal_year=self.fiscal_year, as_dict=True) if not (fiscal_year.year_start_date <= getdate(self.valid_from) \ <= fiscal_year.year_end_date): frappe.throw( _("Valid From date not in Fiscal Year {0}").format( frappe.bold(self.fiscal_year))) if not (fiscal_year.year_start_date <= getdate(self.valid_upto) \ <= fiscal_year.year_end_date): frappe.throw( _("Valid Upto date not in Fiscal Year {0}").format( frappe.bold(self.fiscal_year)))
def get_incomes_expenses_for_period(account, from_date, to_date): """Get amounts for current and past periods""" val = 0.0 balance_on_to_date = get_balance_on(account, date = to_date) balance_before_from_date = get_balance_on(account, date = from_date - timedelta(days=1)) fy_start_date = get_fiscal_year(to_date)[1] if from_date == fy_start_date: val = balance_on_to_date elif from_date > fy_start_date: val = balance_on_to_date - balance_before_from_date else: last_year_closing_balance = get_balance_on(account, date=fy_start_date - timedelta(days=1)) print(fy_start_date - timedelta(days=1), last_year_closing_balance) val = balance_on_to_date + (last_year_closing_balance - balance_before_from_date) return val
def get_actual_data(filters, item_groups, sales_users_or_territory_data, date_field, sales_field): fiscal_year = get_fiscal_year(fiscal_year=filters.get("fiscal_year"), as_dict=1) dates = [fiscal_year.year_start_date, fiscal_year.year_end_date] select_field = "`tab{0}`.{1}".format(filters.get("doctype"), sales_field) child_table = "`tab{0}`".format(filters.get("doctype") + ' Item') if sales_field == 'sales_person': select_field = "`tabSales Team`.sales_person" child_table = "`tab{0}`, `tabSales Team`".format( filters.get("doctype") + ' Item') cond = """`tabSales Team`.parent = `tab{0}`.name and `tabSales Team`.sales_person in ({1}) """.format( filters.get("doctype"), ','.join(['%s'] * len(sales_users_or_territory_data))) else: cond = "`tab{0}`.{1} in ({2})".format( filters.get("doctype"), sales_field, ','.join(['%s'] * len(sales_users_or_territory_data))) return frappe.db.sql(""" SELECT `tab{child_doc}`.item_group, `tab{child_doc}`.stock_qty, `tab{child_doc}`.base_net_amount, {select_field}, `tab{parent_doc}`.{date_field} FROM `tab{parent_doc}`, {child_table} WHERE `tab{child_doc}`.parent = `tab{parent_doc}`.name and `tab{parent_doc}`.docstatus = 1 and {cond} and `tab{child_doc}`.item_group in ({item_groups}) and `tab{parent_doc}`.{date_field} between %s and %s""".format( cond=cond, date_field=date_field, select_field=select_field, child_table=child_table, parent_doc=filters.get("doctype"), child_doc=filters.get("doctype") + ' Item', item_groups=','.join(['%s'] * len(item_groups))), tuple(sales_users_or_territory_data + item_groups + dates), as_dict=1)
def make_period_closing_voucher(self): pcv = frappe.get_doc({ "doctype": "Period Closing Voucher", "closing_account_head": "_Test Account Reserves and Surplus - _TC", "company": "_Test Company", "fiscal_year": get_fiscal_year(today(), company="_Test Company")[0], "posting_date": today(), "cost_center": "_Test Cost Center - _TC", "remarks": "test" }) pcv.insert() pcv.submit() return pcv
def validate_posting_date(self): from erpbee.accounts.utils import get_fiscal_year, validate_fiscal_year validate_fiscal_year(self.posting_date, self.fiscal_year, self.company, label=_("Posting Date"), doc=self) self.year_start_date = get_fiscal_year(self.posting_date, self.fiscal_year, company=self.company)[1] pce = frappe.db.sql( """select name from `tabPeriod Closing Voucher` where posting_date > %s and fiscal_year = %s and docstatus = 1""", (self.posting_date, self.fiscal_year)) if pce and pce[0][0]: frappe.throw( _("Another Period Closing Entry {0} has been made after {1}"). format(pce[0][0], self.posting_date))
def execute(filters=None): filters = filters if isinstance(filters, _dict) else _dict(filters) if not filters: filters.setdefault('fiscal_year', get_fiscal_year(nowdate())[0]) filters.setdefault('company', frappe.db.get_default("company")) region = frappe.db.get_value("Company", fieldname=["country"], filters={"name": filters.company}) if region != 'United States': return [], [] data = [] columns = get_columns() data = frappe.db.sql(""" SELECT s.supplier_group as "supplier_group", gl.party AS "supplier", s.tax_id as "tax_id", SUM(gl.debit_in_account_currency) AS "payments" FROM `tabGL Entry` gl INNER JOIN `tabSupplier` s WHERE s.name = gl.party AND s.irs_1099 = 1 AND gl.fiscal_year = %(fiscal_year)s AND gl.party_type = "Supplier" GROUP BY gl.party ORDER BY gl.party DESC""", { "fiscal_year": filters.fiscal_year, "supplier_group": filters.supplier_group, "company": filters.company }, as_dict=True) return columns, data
def get_sl_entries(self, d, args): sl_dict = frappe._dict({ "item_code": d.get("item_code", None), "warehouse": d.get("warehouse", None), "posting_date": self.posting_date, "posting_time": self.posting_time, 'fiscal_year': get_fiscal_year(self.posting_date, company=self.company)[0], "voucher_type": self.doctype, "voucher_no": self.name, "voucher_detail_no": d.name, "actual_qty": (self.docstatus==1 and 1 or -1)*flt(d.get("stock_qty")), "stock_uom": frappe.db.get_value("Item", args.get("item_code") or d.get("item_code"), "stock_uom"), "incoming_rate": 0, "company": self.company, "batch_no": cstr(d.get("batch_no")).strip(), "serial_no": d.get("serial_no"), "project": d.get("project") or self.get('project'), "is_cancelled": 1 if self.docstatus==2 else 0 }) sl_dict.update(args) return sl_dict
def validate_fiscal_year(from_date, to_date, company): from_fiscal_year = get_fiscal_year(date=from_date, company=company) to_fiscal_year = get_fiscal_year(date=to_date, company=company) if from_fiscal_year != to_fiscal_year: frappe.throw(_('Dates {} and {} are not in the same fiscal year.').format(from_date, to_date))
def validate_expense_against_budget(args): args = frappe._dict(args) if args.get('company') and not args.fiscal_year: args.fiscal_year = get_fiscal_year(args.get('posting_date'), company=args.get('company'))[0] frappe.flags.exception_approver_role = frappe.get_cached_value( 'Company', args.get('company'), 'exception_budget_approver_role') if not args.account: args.account = args.get("expense_account") if not (args.get('account') and args.get('cost_center')) and args.item_code: args.cost_center, args.account = get_item_details(args) if not args.account: return for budget_against in ['project', 'cost_center' ] + get_accounting_dimensions(): if (args.get(budget_against) and args.account and frappe.db.get_value("Account", { "name": args.account, "root_type": "Expense" })): doctype = frappe.unscrub(budget_against) if frappe.get_cached_value('DocType', doctype, 'is_tree'): lft, rgt = frappe.db.get_value(doctype, args.get(budget_against), ["lft", "rgt"]) condition = """and exists(select name from `tab%s` where lft<=%s and rgt>=%s and name=b.%s)""" % ( doctype, lft, rgt, budget_against) #nosec args.is_tree = True else: condition = "and b.%s=%s" % ( budget_against, frappe.db.escape(args.get(budget_against))) args.is_tree = False args.budget_against_field = budget_against args.budget_against_doctype = doctype budget_records = frappe.db.sql(""" select b.{budget_against_field} as budget_against, ba.budget_amount, b.monthly_distribution, ifnull(b.applicable_on_material_request, 0) as for_material_request, ifnull(applicable_on_purchase_order, 0) as for_purchase_order, ifnull(applicable_on_booking_actual_expenses,0) as for_actual_expenses, b.action_if_annual_budget_exceeded, b.action_if_accumulated_monthly_budget_exceeded, b.action_if_annual_budget_exceeded_on_mr, b.action_if_accumulated_monthly_budget_exceeded_on_mr, b.action_if_annual_budget_exceeded_on_po, b.action_if_accumulated_monthly_budget_exceeded_on_po from `tabBudget` b, `tabBudget Account` ba where b.name=ba.parent and b.fiscal_year=%s and ba.account=%s and b.docstatus=1 {condition} """.format(condition=condition, budget_against_field=budget_against), (args.fiscal_year, args.account), as_dict=True) #nosec if budget_records: validate_budget_records(args, budget_records)
def get_dashboard_info(party_type, party, loyalty_program=None): current_fiscal_year = get_fiscal_year(nowdate(), as_dict=True) doctype = "Sales Invoice" if party_type == "Customer" else "Purchase Invoice" companies = frappe.get_all(doctype, filters={ 'docstatus': 1, party_type.lower(): party }, distinct=1, fields=['company']) company_wise_info = [] company_wise_grand_total = frappe.get_all( doctype, filters={ 'docstatus': 1, party_type.lower(): party, 'posting_date': ('between', [ current_fiscal_year.year_start_date, current_fiscal_year.year_end_date ]) }, group_by="company", fields=[ "company", "sum(grand_total) as grand_total", "sum(base_grand_total) as base_grand_total" ]) loyalty_point_details = [] if party_type == "Customer": loyalty_point_details = frappe._dict( frappe.get_all( "Loyalty Point Entry", filters={ 'customer': party, 'expiry_date': ('>=', getdate()), }, group_by="company", fields=["company", "sum(loyalty_points) as loyalty_points"], as_list=1)) company_wise_billing_this_year = frappe._dict() for d in company_wise_grand_total: company_wise_billing_this_year.setdefault( d.company, { "grand_total": d.grand_total, "base_grand_total": d.base_grand_total }) company_wise_total_unpaid = frappe._dict( frappe.db.sql( """ select company, sum(debit_in_account_currency) - sum(credit_in_account_currency) from `tabGL Entry` where party_type = %s and party=%s group by company""", (party_type, party))) for d in companies: company_default_currency = frappe.db.get_value("Company", d.company, 'default_currency') party_account_currency = get_party_account_currency( party_type, party, d.company) if party_account_currency == company_default_currency: billing_this_year = flt( company_wise_billing_this_year.get(d.company, {}).get("base_grand_total")) else: billing_this_year = flt( company_wise_billing_this_year.get(d.company, {}).get("grand_total")) total_unpaid = flt(company_wise_total_unpaid.get(d.company)) if loyalty_point_details: loyalty_points = loyalty_point_details.get(d.company) info = {} info["billing_this_year"] = flt( billing_this_year) if billing_this_year else 0 info["currency"] = party_account_currency info["total_unpaid"] = flt(total_unpaid) if total_unpaid else 0 info["company"] = d.company if party_type == "Customer" and loyalty_point_details: info["loyalty_points"] = loyalty_points if party_type == "Supplier": info["total_unpaid"] = -1 * info["total_unpaid"] company_wise_info.append(info) return company_wise_info