def get_conditions(filters): conditions = [] if filters.get("account"): lft, rgt = frappe.db.get_value("Account", filters["account"], ["lft", "rgt"]) conditions.append("""account in (select name from tabAccount where lft>=%s and rgt<=%s and docstatus<2)""" % (lft, rgt)) if filters.get("cost_center"): filters.cost_center = get_cost_centers_with_children( filters.cost_center) conditions.append("cost_center in %(cost_center)s") if filters.get("voucher_no"): conditions.append("voucher_no=%(voucher_no)s") if filters.get( "group_by") == "Group by Party" and not filters.get("party_type"): conditions.append("party_type in ('Customer', 'Supplier')") if filters.get("party_type"): conditions.append("party_type=%(party_type)s") if filters.get("party"): conditions.append("party in %(party)s") if not (filters.get("account") or filters.get("party") or filters.get("group_by") in ["Group by Account", "Group by Party"]): conditions.append("posting_date >=%(from_date)s") conditions.append("(posting_date <=%(to_date)s or is_opening = 'Yes')") if filters.get("project"): conditions.append("project in %(project)s") if filters.get("finance_book"): if filters.get("include_default_book_entries"): conditions.append( "(finance_book in (%(finance_book)s, %(company_fb)s, '') OR finance_book IS NULL)" ) else: conditions.append("finance_book in (%(finance_book)s)") if not filters.get("show_cancelled_entries"): conditions.append("is_cancelled = 0") from frappe.desk.reportview import build_match_conditions match_conditions = build_match_conditions("GL Entry") if match_conditions: conditions.append(match_conditions) accounting_dimensions = get_accounting_dimensions(as_list=False) if accounting_dimensions: for dimension in accounting_dimensions: if filters.get(dimension.fieldname): if frappe.get_cached_value('DocType', dimension.document_type, 'is_tree'): filters[dimension.fieldname] = get_dimension_with_children( dimension.document_type, filters.get(dimension.fieldname)) conditions.append("{0} in %({0})s".format( dimension.fieldname)) else: conditions.append("{0} in (%({0})s)".format( dimension.fieldname)) return "and {}".format(" and ".join(conditions)) if conditions else ""
def get_conditions(filters): conditions = "" if filters.get("company"): conditions += " and company=%(company)s" if filters.get("customer"): conditions += " and customer = %(customer)s" if filters.get("from_date"): conditions += " and posting_date >= %(from_date)s" if filters.get("to_date"): conditions += " and posting_date <= %(to_date)s" if filters.get("owner"): conditions += " and owner = %(owner)s" if filters.get("mode_of_payment"): conditions += """ and exists(select name from `tabSales Invoice Payment` where parent=`tabSales Invoice`.name and ifnull(`tabSales Invoice Payment`.mode_of_payment, '') = %(mode_of_payment)s)""" if filters.get("cost_center"): conditions += """ and exists(select name from `tabSales Invoice Item` where parent=`tabSales Invoice`.name and ifnull(`tabSales Invoice Item`.cost_center, '') = %(cost_center)s)""" if filters.get("warehouse"): conditions += """ and exists(select name from `tabSales Invoice Item` where parent=`tabSales Invoice`.name and ifnull(`tabSales Invoice Item`.warehouse, '') = %(warehouse)s)""" if filters.get("brand"): conditions += """ and exists(select name from `tabSales Invoice Item` where parent=`tabSales Invoice`.name and ifnull(`tabSales Invoice Item`.brand, '') = %(brand)s)""" if filters.get("item_group"): conditions += """ and exists(select name from `tabSales Invoice Item` where parent=`tabSales Invoice`.name and ifnull(`tabSales Invoice Item`.item_group, '') = %(item_group)s)""" accounting_dimensions = get_accounting_dimensions(as_list=False) if accounting_dimensions: common_condition = """ and exists(select name from `tabSales Invoice Item` where parent=`tabSales Invoice`.name """ for dimension in accounting_dimensions: if filters.get(dimension.fieldname): if frappe.get_cached_value("DocType", dimension.document_type, "is_tree"): filters[dimension.fieldname] = get_dimension_with_children( dimension.document_type, filters.get(dimension.fieldname) ) conditions += ( common_condition + "and ifnull(`tabSales Invoice Item`.{0}, '') in %({0})s)".format(dimension.fieldname) ) else: conditions += ( common_condition + "and ifnull(`tabSales Invoice Item`.{0}, '') in %({0})s)".format(dimension.fieldname) ) return conditions
def get_columns(filters): if filters.get("presentation_currency"): currency = filters["presentation_currency"] else: if filters.get("company"): currency = get_company_currency(filters["company"]) else: company = get_default_company() currency = get_company_currency(company) columns = [ { "label": _("GL Entry"), "fieldname": "gl_entry", "fieldtype": "Link", "options": "GL Entry", "hidden": 1 }, { "label": _("Posting Date"), "fieldname": "posting_date", "fieldtype": "Date", "width": 90 }, { "label": _("Account"), "fieldname": "account", "fieldtype": "Link", "options": "Account", "width": 180 }, { "label": _("Debit ({0})").format(currency), "fieldname": "debit", "fieldtype": "Float", "width": 100 }, { "label": _("Credit ({0})").format(currency), "fieldname": "credit", "fieldtype": "Float", "width": 100 }, { "label": _("Balance ({0})").format(currency), "fieldname": "balance", "fieldtype": "Float", "width": 130 } ] columns.extend([ { "label": _("Voucher Type"), "fieldname": "voucher_type", "width": 120 }, { "label": _("Voucher No"), "fieldname": "voucher_no", "fieldtype": "Dynamic Link", "options": "voucher_type", "width": 180 }, { "label": _("Against Account"), "fieldname": "against", "width": 120 }, { "label": _("Party Type"), "fieldname": "party_type", "width": 100 }, { "label": _("Party"), "fieldname": "party", "width": 100 }, { "label": _("Project"), "options": "Project", "fieldname": "project", "width": 100 } ]) if filters.get("include_dimensions"): for dim in get_accounting_dimensions(as_list = False): columns.append({ "label": _(dim.label), "options": dim.label, "fieldname": dim.fieldname, "width": 100 }) columns.extend([ { "label": _("Cost Center"), "options": "Cost Center", "fieldname": "cost_center", "width": 100 }, { "label": _("Against Voucher Type"), "fieldname": "against_voucher_type", "width": 100 }, { "label": _("Against Voucher"), "fieldname": "against_voucher", "fieldtype": "Dynamic Link", "options": "against_voucher_type", "width": 100 }, { "label": _("Supplier Invoice No"), "fieldname": "bill_no", "fieldtype": "Data", "width": 100 }, { "label": _("Remarks"), "fieldname": "remarks", "width": 400 } ]) return columns
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_rootwise_opening_balances(filters, report_type): additional_conditions = "" if not filters.show_unclosed_fy_pl_balances: additional_conditions = " and posting_date >= %(year_start_date)s" \ if report_type == "Profit and Loss" else "" if not flt(filters.with_period_closing_entry): additional_conditions += " and ifnull(voucher_type, '')!='Period Closing Voucher'" if filters.cost_center: lft, rgt = frappe.db.get_value( 'Cost Center', filters.cost_center, ['lft', 'rgt']) additional_conditions += """ and cost_center in (select name from `tabCost Center` where lft >= %s and rgt <= %s)""" % (lft, rgt) if filters.project: additional_conditions += " and project = %(project)s" if filters.finance_book: fb_conditions = " AND finance_book = %(finance_book)s" if filters.include_default_book_entries: fb_conditions = " AND (finance_book in (%(finance_book)s, %(company_fb)s, '') OR finance_book IS NULL)" additional_conditions += fb_conditions accounting_dimensions = get_accounting_dimensions(as_list=False) query_filters = { "company": filters.company, "from_date": filters.from_date, "report_type": report_type, "year_start_date": filters.year_start_date, "project": filters.project, "finance_book": filters.finance_book, "company_fb": frappe.db.get_value("Company", filters.company, 'default_finance_book') } if accounting_dimensions: for dimension in accounting_dimensions: if filters.get(dimension.fieldname): if frappe.get_cached_value('DocType', dimension.document_type, 'is_tree'): filters[dimension.fieldname] = get_dimension_with_children(dimension.document_type, filters.get(dimension.fieldname)) additional_conditions += "and {0} in %({0})s".format( dimension.fieldname) else: additional_conditions += "and {0} in (%({0})s)".format( dimension.fieldname) query_filters.update({ dimension.fieldname: filters.get(dimension.fieldname) }) gle = frappe.db.sql(""" select account, sum(debit) as opening_debit, sum(credit) as opening_credit from `tabGL Entry` where company=%(company)s {additional_conditions} and (posting_date < %(from_date)s or ifnull(is_opening, 'No') = 'Yes') and account in (select name from `tabAccount` where report_type=%(report_type)s) and is_cancelled = 0 group by account""".format(additional_conditions=additional_conditions), query_filters, as_dict=True) opening = frappe._dict() for d in gle: opening.setdefault(d.account, d) return opening
def make_gl_entries(self): company_currency = frappe.get_cached_value('Company', self.company, "default_currency") gl_entries = [] invoice_fields = [ "debit_to", "party_account_currency", "conversion_rate", "cost_center" ] accounting_dimensions = get_accounting_dimensions() invoice_fields.extend(accounting_dimensions) for d in self.invoices: inv = frappe.db.get_value("Sales Invoice", d.sales_invoice, invoice_fields, as_dict=1) if d.outstanding_amount: outstanding_in_company_currency = flt( d.outstanding_amount * inv.conversion_rate, d.precision("outstanding_amount")) ar_credit_account_currency = frappe.get_cached_value( "Account", self.accounts_receivable_credit, "currency") gl_entries.append( self.get_gl_dict( { "account": inv.debit_to, "party_type": "Customer", "party": d.customer, "against": self.accounts_receivable_credit, "credit": outstanding_in_company_currency, "credit_in_account_currency": outstanding_in_company_currency if inv.party_account_currency == company_currency else d.outstanding_amount, "cost_center": inv.cost_center, "against_voucher": d.sales_invoice, "against_voucher_type": "Sales Invoice" }, inv.party_account_currency, item=inv)) gl_entries.append( self.get_gl_dict( { "account": self.accounts_receivable_credit, "party_type": "Customer", "party": d.customer, "against": inv.debit_to, "debit": outstanding_in_company_currency, "debit_in_account_currency": outstanding_in_company_currency if ar_credit_account_currency == company_currency else d.outstanding_amount, "cost_center": inv.cost_center, "against_voucher": d.sales_invoice, "against_voucher_type": "Sales Invoice" }, ar_credit_account_currency, item=inv)) make_gl_entries(gl_entries, cancel=(self.docstatus == 2), update_outstanding='No')
def make_accrual_jv_entry(self): self.check_permission("write") earnings = self.get_salary_component_total( component_type="earnings") or {} deductions = self.get_salary_component_total( component_type="deductions") or {} payroll_payable_account = self.payroll_payable_account jv_name = "" precision = frappe.get_precision("Journal Entry Account", "debit_in_account_currency") if earnings or deductions: journal_entry = frappe.new_doc("Journal Entry") journal_entry.voucher_type = "Journal Entry" journal_entry.user_remark = _("Accrual Journal Entry for salaries from {0} to {1}")\ .format(self.start_date, self.end_date) journal_entry.company = self.company journal_entry.posting_date = self.posting_date accounting_dimensions = get_accounting_dimensions() or [] accounts = [] currencies = [] payable_amount = 0 multi_currency = 0 company_currency = erpnext.get_company_currency(self.company) # Earnings for acc_cc, amount in earnings.items(): exchange_rate, amt = self.get_amount_and_exchange_rate_for_journal_entry( acc_cc[0], amount, company_currency, currencies) payable_amount += flt(amount, precision) accounts.append( self.update_accounting_dimensions( { "account": acc_cc[0], "debit_in_account_currency": flt(amt, precision), "exchange_rate": flt(exchange_rate), "cost_center": acc_cc[1] or self.cost_center, "project": self.project }, accounting_dimensions)) # Deductions for acc_cc, amount in deductions.items(): exchange_rate, amt = self.get_amount_and_exchange_rate_for_journal_entry( acc_cc[0], amount, company_currency, currencies) payable_amount -= flt(amount, precision) accounts.append( self.update_accounting_dimensions( { "account": acc_cc[0], "credit_in_account_currency": flt(amt, precision), "exchange_rate": flt(exchange_rate), "cost_center": acc_cc[1] or self.cost_center, "project": self.project }, accounting_dimensions)) # Payable amount exchange_rate, payable_amt = self.get_amount_and_exchange_rate_for_journal_entry( payroll_payable_account, payable_amount, company_currency, currencies) accounts.append( self.update_accounting_dimensions( { "account": payroll_payable_account, "credit_in_account_currency": flt( payable_amt, precision), "exchange_rate": flt(exchange_rate), "cost_center": self.cost_center }, accounting_dimensions)) journal_entry.set("accounts", accounts) if len(currencies) > 1: multi_currency = 1 journal_entry.multi_currency = multi_currency journal_entry.title = payroll_payable_account journal_entry.save() try: journal_entry.submit() jv_name = journal_entry.name self.update_salary_slip_status(jv_name=jv_name) except Exception as e: if type(e) in (str, list, tuple): frappe.msgprint(e) raise return jv_name
def create_invoice(self, prorate): """ Creates a `Invoice`, submits it and returns it """ doctype = "Sales Invoice" if self.party_type == "Customer" else "Purchase Invoice" invoice = frappe.new_doc(doctype) # For backward compatibility # Earlier subscription didn't had any company field company = self.get("company") or get_default_company() if not company: frappe.throw( _("Company is mandatory was generating invoice. Please set default company in Global Defaults" )) invoice.company = company invoice.set_posting_time = 1 invoice.posting_date = (self.current_invoice_start if self.generate_invoice_at_period_start else self.current_invoice_end) invoice.cost_center = self.cost_center if doctype == "Sales Invoice": invoice.customer = self.party else: invoice.supplier = self.party if frappe.db.get_value("Supplier", self.party, "tax_withholding_category"): invoice.apply_tds = 1 ### Add party currency to invoice invoice.currency = get_party_account_currency(self.party_type, self.party, self.company) ## Add dimensions in invoice for subscription: accounting_dimensions = get_accounting_dimensions() for dimension in accounting_dimensions: if self.get(dimension): invoice.update({dimension: self.get(dimension)}) # Subscription is better suited for service items. I won't update `update_stock` # for that reason items_list = self.get_items_from_plans(self.plans, prorate) for item in items_list: item["cost_center"] = self.cost_center invoice.append("items", item) # Taxes tax_template = "" if doctype == "Sales Invoice" and self.sales_tax_template: tax_template = self.sales_tax_template if doctype == "Purchase Invoice" and self.purchase_tax_template: tax_template = self.purchase_tax_template if tax_template: invoice.taxes_and_charges = tax_template invoice.set_taxes() # Due date if self.days_until_due: invoice.append( "payment_schedule", { "due_date": add_days(invoice.posting_date, cint(self.days_until_due)), "invoice_portion": 100, }, ) # Discounts if self.is_trialling(): invoice.additional_discount_percentage = 100 else: if self.additional_discount_percentage: invoice.additional_discount_percentage = self.additional_discount_percentage if self.additional_discount_amount: invoice.discount_amount = self.additional_discount_amount if self.additional_discount_percentage or self.additional_discount_amount: discount_on = self.apply_additional_discount invoice.apply_discount_on = discount_on if discount_on else "Grand Total" # Subscription period invoice.from_date = self.current_invoice_start invoice.to_date = self.current_invoice_end invoice.flags.ignore_mandatory = True invoice.set_missing_values() invoice.save() if self.submit_invoice: invoice.submit() return invoice
def get_items_from_plans(self, plans, prorate=0): """ Returns the `Item`s linked to `Subscription Plan` """ if prorate: prorate_factor = get_prorata_factor( self.current_invoice_end, self.current_invoice_start, self.generate_invoice_at_period_start) items = [] party = self.party for plan in plans: plan_doc = frappe.get_doc("Subscription Plan", plan.plan) item_code = plan_doc.item if self.party == "Customer": deferred_field = "enable_deferred_revenue" else: deferred_field = "enable_deferred_expense" deferred = frappe.db.get_value("Item", item_code, deferred_field) if not prorate: item = { "item_code": item_code, "qty": plan.qty, "rate": get_plan_rate(plan.plan, plan.qty, party, self.current_invoice_start, self.current_invoice_end), "cost_center": plan_doc.cost_center, } else: item = { "item_code": item_code, "qty": plan.qty, "rate": get_plan_rate( plan.plan, plan.qty, party, self.current_invoice_start, self.current_invoice_end, prorate_factor, ), "cost_center": plan_doc.cost_center, } if deferred: item.update({ deferred_field: deferred, "service_start_date": self.current_invoice_start, "service_end_date": self.current_invoice_end, }) accounting_dimensions = get_accounting_dimensions() for dimension in accounting_dimensions: if plan_doc.get(dimension): item.update({dimension: plan_doc.get(dimension)}) items.append(item) return items
def get_invoice_dict(self, row=None): def get_item_dict(): cost_center = row.get("cost_center") or frappe.get_cached_value( "Company", self.company, "cost_center") if not cost_center: frappe.throw( _("Please set the Default Cost Center in {0} company."). format(frappe.bold(self.company))) income_expense_account_field = ("income_account" if row.party_type == "Customer" else "expense_account") default_uom = frappe.db.get_single_value("Stock Settings", "stock_uom") or _("Nos") rate = flt(row.outstanding_amount) / flt(row.qty) item_dict = frappe._dict({ "uom": default_uom, "rate": rate or 0.0, "qty": row.qty, "conversion_factor": 1.0, "item_name": row.item_name or "Opening Invoice Item", "description": row.item_name or "Opening Invoice Item", income_expense_account_field: row.temporary_opening_account, "cost_center": cost_center, }) for dimension in get_accounting_dimensions(): item_dict.update({dimension: row.get(dimension)}) return item_dict item = get_item_dict() invoice = frappe._dict({ "items": [item], "is_opening": "Yes", "set_posting_time": 1, "company": self.company, "cost_center": self.cost_center, "due_date": row.due_date, "posting_date": row.posting_date, frappe.scrub(row.party_type): row.party, "is_pos": 0, "doctype": "Sales Invoice" if self.invoice_type == "Sales" else "Purchase Invoice", "update_stock": 0, # important: https://github.com/frappe/erpnext/pull/23559 "invoice_number": row.invoice_number, "disable_rounded_total": 1, }) accounting_dimension = get_accounting_dimensions() for dimension in accounting_dimension: invoice.update( {dimension: self.get(dimension) or item.get(dimension)}) return invoice
def book_revenue_via_journal_entry(doc, credit_account, debit_account, against, amount, base_amount, posting_date, project, account_currency, cost_center, item, deferred_process=None, submit='No'): if amount == 0: return journal_entry = frappe.new_doc('Journal Entry') journal_entry.posting_date = posting_date journal_entry.company = doc.company journal_entry.voucher_type = 'Deferred Revenue' if doc.doctype == 'Sales Invoice' \ else 'Deferred Expense' debit_entry = { 'account': credit_account, 'credit': base_amount, 'credit_in_account_currency': amount, 'party_type': 'Customer' if doc.doctype == 'Sales Invoice' else 'Supplier', 'party': against, 'account_currency': account_currency, 'reference_name': doc.name, 'reference_type': doc.doctype, 'reference_detail_no': item.name, 'cost_center': cost_center, 'project': project, } credit_entry = { 'account': debit_account, 'debit': base_amount, 'debit_in_account_currency': amount, 'party_type': 'Customer' if doc.doctype == 'Sales Invoice' else 'Supplier', 'party': against, 'account_currency': account_currency, 'reference_name': doc.name, 'reference_type': doc.doctype, 'reference_detail_no': item.name, 'cost_center': cost_center, 'project': project, } for dimension in get_accounting_dimensions(): debit_entry.update({ dimension: item.get(dimension) }) credit_entry.update({ dimension: item.get(dimension) }) journal_entry.append('accounts', debit_entry) journal_entry.append('accounts', credit_entry) try: journal_entry.save() if submit: journal_entry.submit() except Exception: frappe.db.rollback() traceback = frappe.get_traceback() frappe.log_error(message=traceback) frappe.flags.deferred_accounting_error = True
def get_conditions(filters, party_type=None): conditions = [] if filters.get("account"): lft, rgt = frappe.db.get_value("Account", filters["account"], ["lft", "rgt"]) conditions.append("""account in (select name from tabAccount where lft>=%s and rgt<=%s and docstatus<2)""" % (lft, rgt)) if filters.get("cost_center"): filters.cost_center = get_cost_centers_with_children( filters.cost_center) conditions.append("cost_center in %(cost_center)s") if filters.get("voucher_no"): conditions.append("voucher_no=%(voucher_no)s") if filters.get( "group_by") == "Group by Party" and not filters.get("party_type"): conditions.append("party_type in ('Customer', 'Supplier')") if filters.get("party_type"): if filters["party_type"] == 'Customer Group' and filters.get( "party") and party_type == None: lft, rgt = frappe.db.get_value("Customer Group", filters["party"][0], ['lft', 'rgt']) get_parent_customer_groups = frappe.db.sql( """select name from `tabCustomer Group` where lft >= %s and rgt <= %s""", (lft, rgt), as_dict=1) customer_groups = [ "%s" % (frappe.db.escape(d.name)) for d in get_parent_customer_groups ] if customer_groups: cond = "and 1=1" customer_group_condition = ",".join( ['%s'] * len(customer_groups)) % (tuple(customer_groups)) condition = "{0} in ({1})".format(' and customer_group', customer_group_condition) cond += condition customer_list = frappe.db.sql( """ select name from `tabCustomer` where docstatus < 2 {cond} """ .format(cond=cond), as_list=1) if len(customer_list) > 0: filters["party"] = customer_list else: filters["party"] = ['1'] filters["party_type"] = 'Customer' conditions.append("party_type=%(party_type)s") else: conditions.append("party_type=%(party_type)s") if filters.get("party"): conditions.append("party in %(party)s") if not (filters.get("account") or filters.get("party") or filters.get("group_by") in ["Group by Account", "Group by Party"]): conditions.append("posting_date >=%(from_date)s") conditions.append("(posting_date <=%(to_date)s or is_opening = 'Yes')") if filters.get("project"): conditions.append("project in %(project)s") if filters.get("finance_book"): if filters.get("include_default_book_entries"): conditions.append( "(finance_book in (%(finance_book)s, %(company_fb)s, '') OR finance_book IS NULL)" ) else: conditions.append("finance_book in (%(finance_book)s)") from frappe.desk.reportview import build_match_conditions match_conditions = build_match_conditions("GL Entry") if match_conditions: conditions.append(match_conditions) accounting_dimensions = get_accounting_dimensions() if accounting_dimensions: for dimension in accounting_dimensions: if filters.get(dimension): conditions.append("{0} in (%({0})s)".format(dimension)) return "and {}".format(" and ".join(conditions)) if conditions else ""
def get_data(filters, conditions): data = [] inc, cond = "", "" query_details = conditions["based_on_select"] + conditions[ "period_wise_select"] posting_date = "t1.transaction_date" if conditions.get("trans") in [ "Sales Invoice", "Purchase Invoice", "Purchase Receipt", "Delivery Note", ]: posting_date = "t1.posting_date" if filters.period_based_on: posting_date = "t1." + filters.period_based_on accounting_dimensions = get_accounting_dimensions(as_list=False) if accounting_dimensions: for dimension in accounting_dimensions: if not dimension.disabled and filters.get(dimension.fieldname): if frappe.get_cached_value("DocType", dimension.document_type, "is_tree"): filters[ dimension.fieldname] = get_dimension_with_children( dimension.document_type, filters.get(dimension.fieldname)) cond += " and t1.{0} in %({0})s".format( dimension.fieldname) else: cond += " and t1.{0} = %({0})s".format( dimension.fieldname) if conditions["based_on_select"] in ["t1.project,", "t2.project,"]: cond = " and " + conditions["based_on_select"][:-1] + " IS Not NULL" if conditions.get("trans") in ["Sales Order", "Purchase Order"]: cond += " and t1.status != 'Closed'" if conditions.get("trans") == "Quotation" and filters.get( "group_by") == "Customer": cond += " and t1.quotation_to = 'Customer'" year_start_date, year_end_date = frappe.db.get_value( "Fiscal Year", filters.get("fiscal_year"), ["year_start_date", "year_end_date"]) if filters.get("group_by"): sel_col = "" ind = conditions["columns"].index(conditions["grbc"][0]) if filters.get("group_by") == "Item": sel_col = "t2.item_code" elif filters.get("group_by") == "Customer": sel_col = ("t1.party_name" if conditions.get("trans") == "Quotation" else "t1.customer") elif filters.get("group_by") == "Supplier": sel_col = "t1.supplier" if filters.get("based_on") in ["Item", "Customer", "Supplier"]: inc = 2 else: inc = 1 data1 = frappe.db.sql( """ select {query_details} from `tab{trans}` t1, `tab{trans} Item` t2 {addl_tables} where t2.parent = t1.name and t1.company = %(company)s and {posting_date} between %(year_start_date)s and %(year_end_date)s and t1.docstatus = 1 {cond} {addl_tables_relational_cond} group by {group_by} """.format( query_details=query_details, trans=conditions["trans"], addl_tables=conditions["addl_tables"], posting_date=posting_date, cond=cond, addl_tables_relational_cond=conditions.get( "addl_tables_relational_cond"), group_by=conditions["group_by"], ), values=filters.update({ "year_start_date": year_start_date, "year_end_date": year_end_date }), as_list=1, ) for d in range(len(data1)): # to add blanck column dt = data1[d] dt.insert(ind, "") data.append(dt) # to get distinct value of col specified by group_by in filter row = frappe.db.sql( """ select DISTINCT({sel_col}) from `tab{trans}` t1, `tab{trans} Item` t2 {addl_tables} where t2.parent = t1.name and t1.company = %(company)s and {posting_date} between %(year_start_date)s and %(year_end_date)s and t1.docstatus = 1 and {group_by} = %(group_by)s {cond} {addl_tables_relational_cond} """.format( sel_col=sel_col, trans=conditions["trans"], addl_tables=conditions["addl_tables"], posting_date=posting_date, cond=cond, addl_tables_relational_cond=conditions.get( "addl_tables_relational_cond"), group_by=conditions["group_by"], ), values=filters.update({ "year_start_date": year_start_date, "year_end_date": year_end_date, "group_by": data1[d][0], }), as_list=1, ) for i in range(len(row)): des = ["" for q in range(len(conditions["columns"]))] # get data for group_by filter row1 = frappe.db.sql( """ select {sel_col} , {period_wise_select} from `tab{trans}` t1, `tab{trans} Item` t2 {addl_tables} where t2.parent = t1.name and t1.company = %(company)s and {posting_date} between %(year_start_date)s and %(year_end_date)s and t1.docstatus = 1 and {sel_col} = %(sel_col)s and {group_by} = %(group_by)s {cond} {addl_tables_relational_cond} """.format( sel_col=sel_col, period_wise_select=conditions["period_wise_select"], trans=conditions["trans"], addl_tables=conditions["addl_tables"], posting_date=posting_date, cond=cond, addl_tables_relational_cond=conditions.get( "addl_tables_relational_cond"), group_by=conditions["group_by"], ), values=filters.update({ "year_start_date": year_start_date, "year_end_date": year_end_date, "sel_col": row[i][0], "group_by": data1[d][0], }), as_list=1, ) des[ind] = row[i][0] for j in range(1, len(conditions["columns"]) - inc): des[j + inc] = row1[0][j] data.append(des) else: data = frappe.db.sql( """ select {query_details} from `tab{trans}` t1, `tab{trans} Item` t2 {addl_tables} where t2.parent = t1.name and t1.company = %(company)s and {posting_date} between %(year_start_date)s and %(year_end_date)s and t1.docstatus = 1 {cond} {addl_tables_relational_cond} group by {group_by} """.format( query_details=query_details, trans=conditions["trans"], addl_tables=conditions["addl_tables"], posting_date=posting_date, cond=cond, addl_tables_relational_cond=conditions.get( "addl_tables_relational_cond", ""), group_by=conditions["group_by"], ), values=filters.update({ "year_start_date": year_start_date, "year_end_date": year_end_date }), as_list=1, ) return data
def get_items_from_plans(self, plans, prorate=0): """ Returns the `Item`s linked to `Subscription Plan` """ if prorate: prorate_factor = get_prorata_factor(self.current_invoice_end, self.current_invoice_start) items = [] party = self.party for plan in plans: plan_doc = frappe.get_doc('Subscription Plan', plan.plan) item_code = plan_doc.item if self.party == 'Customer': deferred_field = 'enable_deferred_revenue' else: deferred_field = 'enable_deferred_expense' deferred = frappe.db.get_value('Item', item_code, deferred_field) if not prorate: item = { 'item_code': item_code, 'qty': plan.qty, 'rate': get_plan_rate(plan.plan, plan.qty, party, self.current_invoice_start, self.current_invoice_end), 'cost_center': plan_doc.cost_center } else: item = { 'item_code': item_code, 'qty': plan.qty, 'rate': get_plan_rate(plan.plan, plan.qty, party, self.current_invoice_start, self.current_invoice_end, prorate_factor), 'cost_center': plan_doc.cost_center } if deferred: item.update({ deferred_field: deferred, 'service_start_date': self.current_invoice_start, 'service_end_date': self.current_invoice_end }) accounting_dimensions = get_accounting_dimensions() for dimension in accounting_dimensions: if plan_doc.get(dimension): item.update({dimension: plan_doc.get(dimension)}) items.append(item) return items
def get_columns(filters): if filters.get("presentation_currency"): currency = filters["presentation_currency"] else: if filters.get("company"): currency = get_company_currency(filters["company"]) else: company = get_default_company() currency = get_company_currency(company) columns = [{ "label": _("GL Entry"), "fieldname": "gl_entry", "fieldtype": "Link", "options": "GL Entry", "hidden": 1 }, { "label": _("Posting Date"), "fieldname": "posting_date", "fieldtype": "Date", "width": 110 }, { "label": _("Account"), "fieldname": "account", "fieldtype": "Link", "options": "Account", "width": 250 }, { "label": _("Debit ({0})").format(currency), "fieldname": "debit", "fieldtype": "Float", "width": 100 }, { "label": _("Credit ({0})").format(currency), "fieldname": "credit", "fieldtype": "Float", "width": 100 }, { "label": _("Balance ({0})").format(currency), "fieldname": "balance", "fieldtype": "Float", "width": 130 }] columns.extend([ { "label": _("Voucher Type"), "fieldname": "voucher_type", "width": 120 }, { "label": _("Voucher No"), "fieldname": "voucher_no", "fieldtype": "Dynamic Link", "options": "voucher_type", "width": 180 }, ]) if filters.get("include_dimensions"): for dim in get_accounting_dimensions(as_list=False): columns.append({ "label": _(dim.label), "options": dim.label, "fieldname": dim.fieldname, "width": 100 }) columns.extend([]) return columns
def get_invoice_dict(self, row=None): def get_item_dict(): default_uom = frappe.db.get_single_value("Stock Settings", "stock_uom") or _("Nos") cost_center = row.get('cost_center') or frappe.get_cached_value( 'Company', self.company, "cost_center") if not cost_center: frappe.throw( _("Please set the Default Cost Center in {0} company."). format(frappe.bold(self.company))) rate = flt(row.outstanding_amount) / flt(row.qty) return frappe._dict({ "uom": default_uom, "rate": rate or 0.0, "qty": row.qty, "conversion_factor": 1.0, "item_name": row.item_name or "Opening Invoice Item", "description": row.item_name or "Opening Invoice Item", income_expense_account_field: row.temporary_opening_account, "cost_center": cost_center }) if not row: return None party_type = "Customer" income_expense_account_field = "income_account" if self.invoice_type == "Purchase": party_type = "Supplier" income_expense_account_field = "expense_account" item = get_item_dict() args = frappe._dict({ "items": [item], "is_opening": "Yes", "set_posting_time": 1, "company": self.company, "cost_center": self.cost_center, "due_date": row.due_date, "posting_date": row.posting_date, frappe.scrub(party_type): row.party, "doctype": "Sales Invoice" if self.invoice_type == "Sales" else "Purchase Invoice", "currency": frappe.get_cached_value('Company', self.company, "default_currency") }) accounting_dimension = get_accounting_dimensions() for dimension in accounting_dimension: args.update({dimension: item.get(dimension)}) if self.invoice_type == "Sales": args["is_pos"] = 0 return args
def create_invoice(self, prorate): """ Creates a `Invoice`, submits it and returns it """ doctype = 'Sales Invoice' if self.party_type == 'Customer' else 'Purchase Invoice' invoice = frappe.new_doc(doctype) invoice.set_posting_time = 1 invoice.posting_date = self.current_invoice_start if self.generate_invoice_at_period_start \ else self.current_invoice_end invoice.cost_center = self.cost_center if doctype == 'Sales Invoice': invoice.customer = self.party else: invoice.supplier = self.party if frappe.db.get_value('Supplier', self.party, 'tax_withholding_category'): invoice.apply_tds = 1 ## Add dimensions in invoice for subscription: accounting_dimensions = get_accounting_dimensions() for dimension in accounting_dimensions: if self.get(dimension): invoice.update({ dimension: self.get(dimension) }) # Subscription is better suited for service items. I won't update `update_stock` # for that reason items_list = self.get_items_from_plans(self.plans, prorate) for item in items_list: invoice.append('items', item) # Taxes tax_template = '' if doctype == 'Sales Invoice' and self.sales_tax_template: tax_template = self.sales_tax_template if doctype == 'Purchase Invoice' and self.purchase_tax_template: tax_template = self.purchase_tax_template if tax_template: invoice.taxes_and_charges = tax_template invoice.set_taxes() # Due date invoice.append( 'payment_schedule', { 'due_date': add_days(invoice.posting_date, cint(self.days_until_due)), 'invoice_portion': 100 } ) # Discounts if self.additional_discount_percentage: invoice.additional_discount_percentage = self.additional_discount_percentage if self.additional_discount_amount: invoice.discount_amount = self.additional_discount_amount if self.additional_discount_percentage or self.additional_discount_amount: discount_on = self.apply_additional_discount invoice.apply_discount_on = discount_on if discount_on else 'Grand Total' # Subscription period invoice.from_date = self.current_invoice_start invoice.to_date = self.current_invoice_end invoice.flags.ignore_mandatory = True invoice.save() invoice.submit() return invoice
def prepare_conditions(self, party_type): conditions = [""] values = [party_type] party_type_field = scrub(party_type) if self.filters.company: conditions.append("company=%s") values.append(self.filters.company) if self.filters.finance_book: conditions.append("ifnull(finance_book,'') in (%s, '')") values.append(self.filters.finance_book) if self.filters.get(party_type_field): conditions.append("party=%s") values.append(self.filters.get(party_type_field)) if party_type_field == "customer": account_type = "Receivable" if self.filters.get("customer_group"): lft, rgt = frappe.db.get_value( "Customer Group", self.filters.get("customer_group"), ["lft", "rgt"]) conditions.append("""party in (select name from tabCustomer where exists(select name from `tabCustomer Group` where lft >= {0} and rgt <= {1} and name=tabCustomer.customer_group))""".format(lft, rgt)) if self.filters.get("territory"): lft, rgt = frappe.db.get_value("Territory", self.filters.get("territory"), ["lft", "rgt"]) conditions.append("""party in (select name from tabCustomer where exists(select name from `tabTerritory` where lft >= {0} and rgt <= {1} and name=tabCustomer.territory))""".format(lft, rgt)) if self.filters.get("payment_terms_template"): conditions.append( "party in (select name from tabCustomer where payment_terms=%s)" ) values.append(self.filters.get("payment_terms_template")) if self.filters.get("sales_partner"): conditions.append( "party in (select name from tabCustomer where default_sales_partner=%s)" ) values.append(self.filters.get("sales_partner")) if self.filters.get("sales_person"): lft, rgt = frappe.db.get_value( "Sales Person", self.filters.get("sales_person"), ["lft", "rgt"]) conditions.append( """exists(select name from `tabSales Team` steam where steam.sales_person in (select name from `tabSales Person` where lft >= {0} and rgt <= {1}) and ((steam.parent = voucher_no and steam.parenttype = voucher_type) or (steam.parent = against_voucher and steam.parenttype = against_voucher_type) or (steam.parent = party and steam.parenttype = 'Customer')))""".format( lft, rgt)) elif party_type_field == "supplier": account_type = "Payable" if self.filters.get("supplier_group"): conditions.append("""party in (select name from tabSupplier where supplier_group=%s)""") values.append(self.filters.get("supplier_group")) if self.filters.get("payment_terms_template"): conditions.append( "party in (select name from tabSupplier where payment_terms=%s)" ) values.append(self.filters.get("payment_terms_template")) if self.filters.get("cost_center"): lft, rgt = frappe.get_cached_value("Cost Center", self.filters.get("cost_center"), ['lft', 'rgt']) conditions.append( """cost_center in (select name from `tabCost Center` where lft >= {0} and rgt <= {1})""".format(lft, rgt)) if self.filters.company: accounts = [ d.name for d in frappe.get_all("Account", filters={ "account_type": account_type, "company": self.filters.company }) ] conditions.append("account in (%s)" % ','.join(['%s'] * len(accounts))) values += accounts accounting_dimensions = get_accounting_dimensions() if accounting_dimensions: for dimension in accounting_dimensions: if self.filters.get(dimension): conditions.append("{0} = %s".format(dimension)) values.append(self.filters.get(dimension)) return " and ".join(conditions), values