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_additional_conditions(from_date, ignore_closing_entries, filters): additional_conditions = [] accounting_dimensions = get_accounting_dimensions(as_list=False) if filters: if filters.get("project"): if not isinstance(filters.get("project"), list): filters.project = frappe.parse_json(filters.get("project")) additional_conditions.append("b.project in %(project)s") if filters.get("cost_center"): filters.cost_center = get_cost_centers_with_children( filters.cost_center) additional_conditions.append("b.cost_center in %(cost_center)s") 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.append("{0} in %({0})s".format( dimension.fieldname)) else: additional_conditions.append("{0} in (%({0})s)".format( dimension.fieldname)) return " and {}".format( " and ".join(additional_conditions)) if additional_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 add_accounting_dimensions_filters(self, conditions, values): accounting_dimensions = get_accounting_dimensions(as_list=False) if accounting_dimensions: for dimension in accounting_dimensions: if self.filters.get(dimension.fieldname): if frappe.get_cached_value('DocType', dimension.document_type, 'is_tree'): self.filters[dimension.fieldname] = get_dimension_with_children(dimension.document_type, self.filters.get(dimension.fieldname)) conditions.append("{0} in %s".format(dimension.fieldname)) values.append(tuple(self.filters.get(dimension.fieldname)))
def get_additional_conditions(from_date, ignore_closing_entries, filters): additional_conditions = [] accounting_dimensions = get_accounting_dimensions(as_list=False) if ignore_closing_entries: additional_conditions.append( "ifnull(voucher_type, '')!='Period Closing Voucher'") if from_date: additional_conditions.append("posting_date >= %(from_date)s") if filters: if filters.get("project"): if not isinstance(filters.get("project"), list): filters.project = frappe.parse_json(filters.get("project")) additional_conditions.append("project in %(project)s") if filters.get("cost_center"): filters.cost_center = get_cost_centers_with_children( filters.cost_center) additional_conditions.append("cost_center in %(cost_center)s") if filters.get("include_default_book_entries"): additional_conditions.append( "(finance_book in (%(finance_book)s, %(company_fb)s, '') OR finance_book IS NULL)" ) else: additional_conditions.append( "(finance_book in (%(finance_book)s, '') OR finance_book IS NULL)" ) 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.append("{0} in %({0})s".format( dimension.fieldname)) else: additional_conditions.append("{0} in (%({0})s)".format( dimension.fieldname)) return " and {}".format( " and ".join(additional_conditions)) if additional_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("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")): conditions.append("posting_date >=%(from_date)s") conditions.append("(posting_date <=%(to_date)s or is_opening = 'Yes')") 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_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 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