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
