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 execute(filters=None): columns, data, message, chart = [], [], [], [] if not filters.get('company'): return columns, data, message, chart period_list = get_period_list(filters.from_date, filters.to_date, filters.periodicity, filters.accumulated_in_group_company) if not filters.get('cost_center'): frappe.msgprint(_("Please select at least one cost center.")) if not filters.get('include_child_cost_centers'): cost_centers = filters.cost_center else: cost_centers = get_cost_centers_with_children(filters.cost_center) columns = get_columns(cost_centers, filters.periodicity, period_list) if filters.get('report') == "Balance Sheet": data, message, chart = get_balance_sheet_data(period_list, cost_centers, columns, filters, cost_center_wise=True) elif filters.get('report') == "Profit and Loss Statement": data, message, chart = get_profit_loss_data(period_list, cost_centers, columns, filters, cost_center_wise=True) return columns, data, message, chart
def 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("customer_group"): conditions.append( "party in (select name from `tabCustomer` where customer_group ='%s')" % filters.get("customer_group")) 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") if filters.get("project"): conditions.append("project in %(project)s") company_finance_book = erpnext.get_default_finance_book( filters.get("company")) if not filters.get("finance_book") or (filters.get("finance_book") == company_finance_book): filters['finance_book'] = company_finance_book conditions.append("ifnull(finance_book, '') in (%(finance_book)s, '')") elif filters.get("finance_book"): conditions.append("ifnull(finance_book, '') = %(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) return "and {}".format(" and ".join(conditions)) if conditions else ""
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") if filters.get("project"): conditions.append("project in %(project)s") company_finance_book = erpnext.get_default_finance_book(filters.get("company")) if not filters.get("finance_book") or (filters.get("finance_book") == company_finance_book): filters['finance_book'] = company_finance_book conditions.append("ifnull(finance_book, '') in (%(finance_book)s, '')") elif filters.get("finance_book"): conditions.append("ifnull(finance_book, '') = %(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) return "and {}".format(" and ".join(conditions)) if conditions else ""
def execute(filters=None): columns, data, message, chart = [], [], [], [] if not filters.get('company'): return columns, data, message, chart period_dict = {} period_dict.update({ 'year_start_date': filters.get('from_date'), 'year_end_date': filters.get('to_date') }) if not filters.get('cost_center'): frappe.msgprint(_("Please select at least one cost center.")); cost_centers = get_cost_centers_with_children(filters.cost_center) columns = get_columns(cost_centers) if filters.get('report') == "Balance Sheet": data, message, chart = get_balance_sheet_data(period_dict, cost_centers, columns, filters, cost_center_wise=True) elif filters.get('report') == "Profit and Loss Statement": data, message, chart = get_profit_loss_data(period_dict, cost_centers, columns, filters, cost_center_wise=True) return columns, data, message, chart
def get_conditions(self): conditions = [] if self.filters.get("customer"): conditions.append("s.customer=%(customer)s") if self.filters.get("customer_group"): lft, rgt = frappe.db.get_value("Customer Group", self.filters.customer_group, ["lft", "rgt"]) conditions.append( """s.customer_group in (select name from `tabCustomer Group` where lft>=%s and rgt<=%s and docstatus<2)""" % (lft, rgt)) if self.filters.get("supplier"): conditions.append("s.supplier=%(supplier)s") if self.filters.get("supplier_group"): lft, rgt = frappe.db.get_value("Supplier Group", self.filters.supplier_group, ["lft", "rgt"]) conditions.append( """sup.supplier_group in (select name from `tabSupplier Group` where lft>=%s and rgt<=%s and docstatus<2)""" % (lft, rgt)) if self.filters.get("item_code"): conditions.append("i.item_code=%(item_code)s") if self.filters.get("item_group"): lft, rgt = frappe.db.get_value("Item Group", self.filters.item_group, ["lft", "rgt"]) conditions.append( """i.item_group in (select name from `tabItem Group` where lft>=%s and rgt<=%s and docstatus<2)""" % (lft, rgt)) if self.filters.get("brand"): conditions.append("i.brand=%(brand)s") if self.filters.get("territory"): lft, rgt = frappe.db.get_value("Territory", self.filters.territory, ["lft", "rgt"]) conditions.append( """s.territory in (select name from `tabTerritory` where lft>=%s and rgt<=%s and docstatus<2)""" % (lft, rgt)) if self.filters.get("sales_person"): lft, rgt = frappe.db.get_value("Sales Person", self.filters.sales_person, ["lft", "rgt"]) conditions.append( """sp.sales_person in (select name from `tabSales Person` where lft>=%s and rgt<=%s and docstatus<2)""" % (lft, rgt)) if self.filters.get("order_type"): conditions.append("s.order_type=%(order_type)s") if self.filters.get("cost_center"): self.filters.cost_center = get_cost_centers_with_children( self.filters.get("cost_center")) conditions.append("i.cost_center in %(cost_center)s") if self.filters.get("project"): projects = cstr(self.filters.get("project")).strip() self.filters.project = [ d.strip() for d in projects.split(',') if d ] conditions.append("i.project in %(project)s" if frappe.get_meta( self.filters.doctype + " Item").has_field("project") else "s.project in %(project)s") return "and {}".format(" and ".join(conditions)) if conditions else ""
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"): voucher_filter_method = filters.get("voucher_filter_method") if voucher_filter_method == "Posted Against Voucher": conditions.append("against_voucher=%(voucher_no)s") elif voucher_filter_method == "Posted By and Against Voucher": conditions.append( "voucher_no=%(voucher_no)s or against_voucher=%(voucher_no)s") else: conditions.append("voucher_no=%(voucher_no)s") if filters.get("against_voucher"): conditions.append("against_voucher=%(against_voucher)s") if filters.get("reference_no"): conditions.append("reference_no=%(reference_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 filters.get("account") or filters.get("party") \ or filters.get("group_by") in [_("Group by Account"), _("Group by Party")]: conditions.append( "(posting_date <= %(to_date)s or is_opening = 'Yes')") else: conditions.append("posting_date between %(from_date)s and %(to_date)s") if not filters.get('show_opening_entries'): conditions.append("is_opening != 'Yes'") if filters.get("project"): conditions.append("project in %(project)s") if filters.get("sales_person"): lft, rgt = frappe.db.get_value("Sales Person", 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 = party and steam.parenttype = party_type)""".format( lft, rgt)) if filters.get("finance_book"): conditions.append("ifnull(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) return "and {}".format(" and ".join(conditions)) if conditions else ""
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 ""