def _get_data(clauses, values, keys): result = frappe.db.sql( """ SELECT e.bank_name AS bank_name, e.bank_ac_no AS bank_ac_no, e.employee_name AS employee_name, sl.rounded_total AS amount, sl.start_date AS start_date, a.account_number AS account_number FROM `tabSalary Slip` AS sl LEFT JOIN `tabEmployee` AS e ON e.name = sl.employee LEFT JOIN `tabPayroll Entry` AS pe ON pe.name = sl.payroll_entry LEFT JOIN `tabAccount` AS a ON a.name = pe.payment_account WHERE {clauses} """.format(clauses=clauses), values=values, as_dict=1, ) def add_remarks(row): start_date = row.get("start_date") return merge( row, {"remarks": "{} SAL".format(start_date.strftime("%b").upper())}) make_row = compose(partial(pick, keys), add_remarks) return with_report_generation_time([make_row(x) for x in result], keys)
def _get_data(clauses, values, keys): items = frappe.db.sql( """ SELECT b.item_code AS item_code, i.item_name AS item_name, b.actual_qty AS actual_qty, b.reserved_qty AS reserved_qty, b.projected_qty AS projected_qty, b.warehouse FROM `tabBin` AS b LEFT JOIN `tabItem` AS i ON i.item_code = b.item_code WHERE {clauses} ORDER BY b.item_code """.format( clauses=clauses ), values=values, as_dict=1, ) template = reduce(lambda a, x: merge(a, {x: None}), keys, {}) make_row = compose( partial(pick, keys), partial(valmap, lambda x: x or None), partial(merge, template), ) return with_report_generation_time([make_row(x) for x in items], keys)
def _get_data(clauses, values, keys): rows = frappe.db.sql( """ SELECT si.os_branch AS branch, SUM(sii.qty) AS qty_sold, SUM(ip.price_list_rate * sii.qty) AS cost_price, SUM(sii.amount) AS sale_amount FROM `tabSales Invoice` AS si RIGHT JOIN `tabSales Invoice Item` AS sii ON sii.parent = si.name LEFT JOIN `tabItem Price` AS ip ON {price_clauses} WHERE {clauses} GROUP BY si.os_branch """.format(**clauses), values=values, as_dict=1, ) def set_cost_pc(row): cost_pc = ((row.get("cost_price") or 0) / row.get("sale_amount") * 100 if row.get("sale_amount") else 0) return merge(row, {"cost_pc": cost_pc}) make_row = compose(partial(pick, keys), set_cost_pc) return with_report_generation_time([make_row(x) for x in rows], keys)
def _get_data(clauses, args, keys): items = frappe.db.sql( """ SELECT si.customer AS customer, sii.item_code AS item_code, sii.item_name AS item_name, SUM(sii.qty) AS qty, SUM(sii.amount) AS gross FROM `tabSales Invoice Item` AS sii LEFT JOIN `tabSales Invoice` AS si ON sii.parent = si.name WHERE {clauses} GROUP BY si.customer, sii.item_code """.format( clauses=clauses ), values=args, as_dict=1, ) def add_rate(row): return merge(row, {"rate": row.gross / row.qty}) make_row = compose(partial(keyfilter, lambda k: k in keys), add_rate) return with_report_generation_time([make_row(x) for x in items], keys)
def _get_data(clauses, values, keys): items = frappe.db.sql( """ SELECT i.brand AS brand, i.item_code AS item_code, i.item_group AS item_group, i.item_name AS item_name, ipss.price_list_rate AS standard_selling, SUM(b.actual_qty) AS qty, ipms.price_list_rate AS minimum_selling FROM `tabItem` AS i LEFT JOIN `tabBin` AS b ON {bin_clauses} LEFT JOIN ({standard_selling_sq}) AS ipss ON ipss.item_code = i.item_code LEFT JOIN ({minimum_selling_sq}) AS ipms ON ipms.item_code = i.item_code WHERE {clauses} GROUP BY i.item_code """.format(standard_selling_sq=price_sq("Standard Selling"), minimum_selling_sq=price_sq("Minimum Selling"), **clauses), values=values, as_dict=1, ) make_row = partial(pick, keys) return with_report_generation_time([make_row(x) for x in items], keys)
def _get_data(clauses, values, keys): rows = frappe.db.sql( """ SELECT c.name AS customer, c.customer_name AS customer_name, c.os_cpr_no AS cpr_no, c.os_mobile_number AS mobile_no, c.os_loyalty_card_no AS loyalty_card_no, c.os_loyalty_activation_date AS activation_date, c.loyalty_program AS loyalty_program, c.loyalty_program_tier AS tier, lpe.loyalty_points AS points FROM `tabCustomer` AS c LEFT JOIN ( SELECT customer, loyalty_program, SUM(loyalty_points) AS loyalty_points FROM `tabLoyalty Point Entry` WHERE expiry_date >= %(today)s GROUP BY customer, loyalty_program ) AS lpe ON lpe.customer = c.name AND lpe.loyalty_program = c.loyalty_program WHERE IFNULL(c.loyalty_program, '') != '' ORDER BY c.os_loyalty_activation_date """, values={"today": today()}, as_dict=1, ) make_row = partial(pick, keys) return with_report_generation_time([make_row(x) for x in rows], keys)
def _get_data(clauses, values, keys): result = frappe.db.sql( """ SELECT osri.sales_no AS sales_no, osri.date AS date, osri.description AS description, osri.qty AS qty, osri.item_sold_at AS item_sold_at, osri.total_sales_invoice AS total_sales_invoice, osri.sales_person AS sales_person, osr.customer_name AS customer_name, osr.customer AS customer, osr.old_customer_id AS old_customer_id, osri.branch_code AS branch FROM `tabOld Sales Record Item` AS osri LEFT JOIN `tabOld Sales Record` AS osr ON osr.name = osri.parent WHERE {clauses} ORDER BY osri.date """.format( clauses=clauses ), values=values, as_dict=1, ) make_row = compose(partial(pick, keys)) return with_report_generation_time([make_row(x) for x in result], keys)
def _get_data(clauses, values, keys, query): rows = frappe.db.sql(query.format(clauses=clauses), values=values, as_dict=1) make_row = compose(partial(pick, keys), _set_mops(rows), _set_sales_orders(rows)) return with_report_generation_time([make_row(x) for x in rows], keys)
def _get_data(clauses, values, keys): items = frappe.db.sql( """ SELECT branch.name AS branch, i.item_group AS item_group, i.brand AS brand, bin.item_code AS item_code, i.item_name AS item_name, SUM(bin.projected_qty) AS qty_balance FROM `tabBin` AS bin LEFT JOIN `tabBranch` as branch ON branch.warehouse = bin.warehouse LEFT JOIN `tabItem` AS i ON i.name = bin.item_code WHERE {clauses} GROUP BY {group_by} """.format( **clauses ), values=values, as_dict=1, ) sold_items = frappe.db.sql( """ SELECT sii.item_code AS item_code, si.os_branch AS branch, SUM(sii.qty) AS qty_sold FROM `tabSales Invoice Item` AS sii LEFT JOIN `tabSales Invoice` AS si ON si.name = sii.parent WHERE {si_clauses} GROUP BY {si_group_by} """.format( **clauses ), values=values, as_dict=1, ) width_branch = not not values.get("branches") make_row = compose( partial(pick, keys), _make_add_qty_sold(sold_items, width_branch) ) make_data = compose( list, partial( filter, lambda x: x.get("qty_sold", 0) != 0 or x.get("qty_balance", 0) != 0 ), partial(map, make_row), ) return with_report_generation_time(make_data(items), keys)
def _get_data(clauses, values, keys): sles = frappe.db.sql( """ SELECT sle.batch_no AS batch_no, sle.item_code AS item_code, SUM(sle.actual_qty) AS qty, sle.warehouse AS warehouse, i.item_name AS item_name, i.item_group AS item_group, i.brand AS brand, id.default_supplier AS supplier, b.expiry_date AS expiry_date, p1.price_list_rate AS buying_price, p2.price_list_rate AS selling_price FROM `tabStock Ledger Entry` AS sle LEFT JOIN `tabItem` AS i ON i.item_code = sle.item_code LEFT JOIN `tabBatch` AS b ON b.batch_id = sle.batch_no LEFT JOIN `tabItem Price` AS p1 ON p1.item_code = sle.item_code AND p1.price_list = %(buying_price_list)s LEFT JOIN `tabItem Price` AS p2 ON p2.item_code = sle.item_code AND p2.price_list = %(selling_price_list)s LEFT JOIN `tabItem Default` AS id ON id.parent = sle.item_code AND id.company = sle.company WHERE {clauses} GROUP BY sle.batch_no, sle.warehouse ORDER BY sle.item_code, sle.warehouse """.format(clauses=clauses), values=values, as_dict=1, ) def set_expiry(row): expiry_in_days = (row.expiry_date - getdate()).days if row.expiry_date else None return merge(row, {"expiry_in_days": expiry_in_days}) make_row = compose(partial(pick, keys), set_expiry) def row_filter(row): if not values.get("hide_zero_stock"): return True return row.get("qty") > 0 return with_report_generation_time( [make_row(x) for x in sles if row_filter(x)], keys)
def _get_data(clauses, values, keys): # UNION ALL for minor performance gain result = frappe.db.sql( """ SELECT si.posting_date AS posting_date, si.posting_time AS posting_time, 'Sales Invoice' AS voucher_type, si.name AS voucher_no, sip.mode_of_payment AS mode_of_payment, sip.amount AS paid_amount, si.customer AS customer, si.customer_name AS customer_name, si.os_sales_person AS sales_person, si.os_sales_person_name AS sales_person_name, si.os_branch AS branch FROM `tabSales Invoice` AS si RIGHT JOIN `tabSales Invoice Payment` AS sip ON sip.parent = si.name WHERE {si_clauses} UNION ALL SELECT pe.posting_date AS posting_date, pe.os_posting_time AS posting_time, 'Payment Entry' AS voucher_type, pe.name AS voucher_no, pe.mode_of_payment AS mode_of_payment, pe.paid_amount AS paid_amount, pe.party AS customer, pe.party_name AS customer_name, per.sales_person AS sales_person, per.sales_person_name AS sales_person_name, pe.os_branch AS branch FROM `tabPayment Entry` AS pe RIGHT JOIN ( SELECT rjper.parent AS parent, rjsi.os_sales_person AS sales_person, rjsi.os_sales_person_name AS sales_person_name FROM `tabPayment Entry Reference` AS rjper LEFT JOIN `tabSales Invoice` AS rjsi ON rjsi.name = rjper.reference_name WHERE rjper.reference_doctype = 'Sales Invoice' ) AS per ON per.parent = pe.name WHERE {pe_clauses} ORDER BY posting_date, posting_time """.format(**clauses), values=values, as_dict=1, ) make_row = partial(pick, keys) return with_report_generation_time([make_row(x) for x in result], keys)
def _get_data(clauses, values, keys): items = frappe.db.sql( """ SELECT i.item_group AS item_group, i.brand AS brand, i.item_code AS item_code, i.item_name AS item_name, ipsb.price_list_rate AS cost_price, ipms.price_list_rate AS minimum_selling, ipss.price_list_rate AS standard_selling FROM `tabItem` AS i LEFT JOIN ({standard_buying_sq}) AS ipsb ON ipsb.item_code = i.item_code LEFT JOIN ({minimum_selling_sq}) AS ipms ON ipms.item_code = i.item_code LEFT JOIN ({standard_selling_sq}) AS ipss ON ipss.item_code = i.item_code WHERE {clauses} """.format( clauses=clauses, standard_buying_sq=price_sq("Standard Buying"), minimum_selling_sq=price_sq("Minimum Selling"), standard_selling_sq=price_sq("Standard Selling"), ), values=values, as_dict=1, ) bins = frappe.db.sql( """ SELECT b.item_code AS item_code, b.projected_qty AS qty, w.branch AS branch FROM `tabBin` AS b LEFT JOIN `tabBranch` AS w ON w.warehouse = b.warehouse WHERE b.item_code IN %(items)s """, values={"items": list(pluck("item_code", items))}, as_dict=1, ) template = reduce(lambda a, x: merge(a, {x: None}), keys, {}) make_row = compose( partial(valmap, lambda x: x or None), partial(pick, keys), partial(merge, template), _set_qty(bins), ) return with_report_generation_time([make_row(x) for x in items], keys)
def _get_data(clauses, values, keys): items = frappe.db.sql( """ SELECT i.brand AS brand, i.item_group AS item_group, SUM(b.projected_qty) AS qty FROM `tabItem` AS i LEFT JOIN `tabBin` AS b ON {bin_clauses} WHERE {clauses} GROUP BY i.brand, i.item_group """.format( **clauses ), values=values, as_dict=1, ) make_row = partial(pick, keys) return with_report_generation_time([make_row(x) for x in items], keys)
def _get_data(clauses, values, keys): get_sales_orders = compose(list, unique, partial(pluck, "name"), frappe.db.sql) sales_orders = get_sales_orders( """ SELECT docname AS name FROM `tabVersion` WHERE ref_doctype = 'Sales Order' AND INSTR(data, 'comment_type') > 0 AND INSTR(data, 'Workflow') > 0 AND creation BETWEEN %(from_date)s AND %(to_date)s UNION ALL SELECT name FROM `tabSales Order` WHERE creation BETWEEN %(from_date)s AND %(to_date)s """, values=values, as_dict=1, ) result = frappe.db.sql( """ SELECT so.name AS sales_order, so.creation AS creation, e.employee_name AS lab_tech, so.grand_total AS total FROM `tabSales Order` AS so LEFT JOIN `tabEmployee` AS e ON e.name = so.os_lab_tech WHERE so.name IN %(sales_orders)s AND {clauses} ORDER BY so.creation """.format(clauses=clauses), values=merge(values, {"sales_orders": sales_orders}), as_dict=1, ) set_outstanding = _set_outstanding_amounts(result) set_workflow_updates = _set_workflow_updates(result) make_row = compose(partial(pick, keys), set_workflow_updates, set_outstanding) return with_report_generation_time([make_row(x) for x in result], keys)
def _get_data(clauses, args, keys): items = frappe.db.sql( """ SELECT posting_date, name AS invoice, supplier, base_total AS total, base_discount_amount AS discount, base_net_total AS net_total, base_total_taxes_and_charges AS tax, base_grand_total AS grand_total FROM `tabPurchase Invoice` WHERE {clauses} """.format(clauses=clauses), values=args, as_dict=1, ) make_row = partial(keyfilter, lambda k: k in keys) return with_report_generation_time([make_row(x) for x in items], keys)
def _get_data(data, keys): def get_reference_st(stock_entry): return frappe.db.exists( "Stock Transfer", {"outgoing_stock_entry": stock_entry} ) or frappe.db.exists("Stock Transfer", {"outgoing_stock_entry": stock_entry}) def add_fields(row): if row.voucher_type == "Stock Entry": purpose = frappe.db.get_value("Stock Entry", row.voucher_no, "purpose") return frappe._dict( merge( row, { "purpose": purpose, "reference_stock_transfer": get_reference_st(row.voucher_no) if purpose == "Material Transfer" else None, }, ) ) return row return with_report_generation_time([add_fields(x) for x in data], keys)
def _get_data(clauses, values, keys, intervals): branches = frappe.db.sql( """ SELECT branch FROM `tabBranch` WHERE {branch_clause} """.format(**clauses), values=values, as_dict=1, ) customers = frappe.db.sql( """ SELECT branch, os_loyalty_activation_date AS loyalty_activation_date FROM `tabCustomer` WHERE {customer_clause} """.format(**clauses), values=values, as_dict=1, ) make_row = compose(partial(pick, keys), _count_activations(customers, intervals)) return with_report_generation_time([make_row(x) for x in branches], keys)
def _get_data(clauses, values, keys): docs = frappe.db.sql( """ SELECT st.name AS name, st.outgoing_datetime AS outgoing_datetime, st.incoming_datetime AS incoming_datetime, st.source_branch AS source_branch, st.target_branch AS target_branch, sti.item_code AS item_code, sti.item_name AS item_name, sti.qty AS qty, st.outgoing_stock_entry AS outgoing_stock_entry, st.incoming_stock_entry AS incoming_stock_entry, st.workflow_state AS workflow_state FROM `tabStock Transfer` AS st RIGHT JOIN `tabStock Transfer Item` AS sti ON sti.parent = st.name WHERE {clauses} """.format(clauses=clauses), values, as_dict=1, ) def add_dates(x): return merge( x, { "outgoing_date": x.outgoing_datetime.date() if x.outgoing_datetime else None, "incoming_date": x.incoming_datetime.date() if x.incoming_datetime else None, }, ) make_row = compose(partial(pick, keys), add_dates) return with_report_generation_time([make_row(x) for x in docs], keys)
def _get_data(clauses, values, keys): rows = frappe.db.sql( """ SELECT c.os_loyalty_card_no AS loyalty_card_no, lpe.customer AS customer, c.customer_name AS customer_name, c.os_cpr_no AS cpr_no, c.os_mobile_number AS mobile_no, c.os_loyalty_activation_date AS activation_date, c.loyalty_program AS loyalty_program, c.loyalty_program_tier AS tier, lpe.loyalty_points AS points_earned, -lper.loyalty_points AS points_redeemed, lpe.posting_date AS date_earned, lpe.expiry_date AS expiry_date FROM `tabLoyalty Point Entry` AS lpe LEFT JOIN `tabCustomer` AS c ON c.name = lpe.customer LEFT JOIN ( SELECT redeem_against, SUM(loyalty_points) AS loyalty_points FROM `tabLoyalty Point Entry` WHERE IFNULL(redeem_against, '') != '' GROUP BY redeem_against ) AS lper ON lper.redeem_against = lpe.name WHERE {clauses} """.format( clauses=clauses ), values=values, as_dict=1, ) make_row = partial(pick, keys) return with_report_generation_time([make_row(x) for x in rows], keys)
def _get_data(clauses, values, keys): def get_query(query): return frappe.db.sql(query.format(**clauses), values=values, as_dict=1) get_opening = compose(lambda x: x.opening, first, get_query) opening = get_opening(""" SELECT SUM(loyalty_points) AS opening FROM `tabLoyalty Point Entry` WHERE {opening_clause} """) rows = get_query(""" SELECT posting_date, sales_invoice, os_custom_loyalty_entry AS custom_loyalty_entry, loyalty_points AS points FROM `tabLoyalty Point Entry` WHERE {period_clause} ORDER BY posting_date """) def set_voucher_ref(row): if row.get("sales_invoice"): return merge( row, { "voucher_type": "Sales Invoice", "voucher_no": row.get("sales_invoice"), }, ) if row.get("custom_loyalty_entry"): return merge( row, { "voucher_type": "Custom Loyalty Entry", "voucher_no": row.get("custom_loyalty_entry"), }, ) return row def set_balance(a, row): return merge(row, {"balance": a.get("balance", 0) + row.get("points")}) make_list = compose(list, concatv) return with_report_generation_time( make_list( accumulate( set_balance, [set_voucher_ref(x) for x in rows], initial={ "voucher_no": "Opening", "balance": opening }, ), [{ "voucher_no": "Total", "points": sum([x.points for x in rows]) }], ), keys, )
def _get_data(args, columns): warehouse_conditions = ("warehouse = %(warehouse)s" if args.get( "warehouse" ) else ( "warehouse IN (SELECT name FROM `tabWarehouse` WHERE company = %(company)s)" )) items = frappe.db.sql( """ SELECT i.item_code AS item_code, i.brand AS brand, i.item_name AS item_name, id.default_supplier AS supplier, p.price_list_rate AS price, b.actual_qty AS stock FROM `tabItem` AS i LEFT JOIN `tabItem Price` AS p ON p.item_code = i.item_code AND p.price_list = %(price_list)s LEFT JOIN ( SELECT item_code, SUM(actual_qty) AS actual_qty FROM `tabBin` WHERE {warehouse_conditions} GROUP BY item_code ) AS b ON b.item_code = i.item_code LEFT JOIN `tabItem Default` AS id ON id.parent = i.name AND id.company = %(company)s """.format(warehouse_conditions=warehouse_conditions), values={ "price_list": args.get("price_list"), "company": args.get("company"), "warehouse": args.get("warehouse"), }, as_dict=1, ) sles = frappe.db.sql( """ SELECT item_code, posting_date, actual_qty FROM `tabStock Ledger Entry` WHERE docstatus < 2 AND voucher_type = 'Sales Invoice' AND company = %(company)s AND {warehouse_conditions} AND posting_date BETWEEN %(start_date)s AND %(end_date)s """.format(warehouse_conditions=warehouse_conditions), values={ "company": args.get("company"), "warehouse": args.get("warehouse"), "start_date": args.get("start_date"), "end_date": args.get("end_date"), }, as_dict=1, ) keys = compose(list, partial(pluck, "fieldname"))(columns) periods = list( filter(lambda x: x.get("start_date") and x.get("end_date"), columns)) set_consumption = _set_consumption(sles, periods) make_row = compose(partial(keyfilter, lambda k: k in keys), set_consumption) return with_report_generation_time([make_row(x) for x in items], keys)
def _get_data(clauses, values, keys, query): rows = frappe.db.sql(query.format(clauses=clauses), values=values, as_dict=1) make_row = partial(pick, keys) return with_report_generation_time([make_row(x) for x in rows], keys)
def _get_data(clauses, values, keys): items = frappe.db.sql( """ SELECT i.item_group AS item_group, i.brand AS brand, i.name AS item_code, i.item_name AS item_name, SUM(bin.projected_qty) AS current_qty FROM `tabItem` AS i LEFT JOIN `tabBin` AS bin ON bin.item_code = i.name WHERE {item_clauses} GROUP BY i.item_code """.format(**clauses), values=values, as_dict=1, ) sles = frappe.db.sql( """ SELECT sle.item_code AS item_code, sle.voucher_type AS voucher_type, pi.is_return AS purchase_invoice_is_return, pr.is_return AS purchase_receipt_is_return, se.purpose AS stock_entry_purpose, st.workflow_state AS stock_transfer_status, sle.actual_qty AS qty FROM `tabStock Ledger Entry` AS sle LEFT JOIN `tabStock Entry` AS se ON se.name = sle.voucher_no LEFT JOIN `tabPurchase Invoice` AS pi ON pi.name = sle.voucher_no LEFT JOIN `tabPurchase Receipt` AS pr ON pr.name = sle.voucher_no LEFT JOIN `tabStock Transfer` AS st ON st.name = se.os_reference_stock_transfer WHERE {sle_clauses} """.format(**clauses), values=values, as_dict=1, ) def set_total(row): def get(field): return row.get(field) or 0 return merge( row, { "total": get("opening_qty") + get("purchased_qty") + get("transit_qty") - get("returned_qty") - get("adjustments") - get("sold_qty") }, ) make_row = compose(partial(pick, keys), set_total, _set_qtys(sles), _set_opening(values)) return with_report_generation_time([make_row(x) for x in items], keys)
def _get_data(clauses, values, keys): items = frappe.db.sql( """ SELECT s.name AS sales_invoice, s.posting_time AS posting_time, s.is_return AS is_return, s.return_against AS return_against, s.customer AS customer, s.customer_name AS customer_name, s.total_qty AS total_qty, s.base_net_total AS net_total, s.base_total_taxes_and_charges AS tax_total, s.base_grand_total AS grand_total, s.outstanding_amount AS outstanding_amount, s.os_sales_person AS sales_person, e.employee_name AS sales_person_name FROM `tabSales Invoice` AS s LEFT JOIN `tabEmployee` AS e ON e.name = s.os_sales_person WHERE {clauses} """.format(clauses=clauses), values=values, as_dict=1, ) payments = frappe.db.sql( """ SELECT p.parent AS sales_invoice, p.mode_of_payment AS mode_of_payment, p.base_amount AS amount FROM `tabSales Invoice` as s LEFT JOIN `tabSales Invoice Payment` as p ON p.parent = s.name WHERE {clauses} """.format(clauses=clauses), values=values, as_dict=1, ) collection = frappe.db.sql( """ SELECT COUNT(s.name) AS pe_count, SUM(s.paid_amount) AS pe_amount FROM `tabPayment Entry` AS s WHERE s.payment_type = 'Receive' AND s.party_type = 'Customer' AND {clauses} """.format(clauses=clauses), values=values, as_dict=1, ) template = reduce(lambda a, x: merge(a, {x: None}), keys, {}) make_row = compose( partial(valmap, lambda x: x or None), partial(pick, keys), partial(merge, template), _set_payments(payments), ) make_mops = compose(lambda x: {"mops": x}, unique, partial(pluck, "mode_of_payment")) make_pe = excepts("StopIteration", first, {"pe_count": 0, "pe_amount": 0}) return ( with_report_generation_time([make_row(x) for x in items], keys), merge(make_mops(payments), make_pe(collection)), )
def _get_data(clauses, values, keys): result = frappe.db.sql( """ SELECT e.bank_name AS bank_name, e.bank_ac_no AS bank_ac_no, e.employee_name AS employee_name, sl.name AS salary_slip, sl.start_date AS start_date, a.account_number AS account_number FROM `tabSalary Slip` AS sl LEFT JOIN `tabEmployee` AS e ON e.name = sl.employee LEFT JOIN `tabPayroll Entry` AS pe ON pe.name = sl.payroll_entry LEFT JOIN `tabAccount` AS a ON a.name = pe.payment_account WHERE {clauses} """.format( clauses=clauses ), values=values, as_dict=1, ) get_amounts = compose( partial(groupby, "salary_slip"), lambda type: frappe.db.sql( """ SELECT sl.name AS salary_slip, SUM(sd.amount) AS amount FROM `tabSalary Detail` AS sd LEFT JOIN `tabSalary Slip` AS sl ON sl.name = sd.parent WHERE sd.parentfield = %(parentfield)s AND sd.parent IN %(salary_slips)s AND sd.salary_component IN %(components)s GROUP BY sl.name """, values=merge( values, { "salary_slips": [x.get("salary_slip") for x in result], "parentfield": type, }, ), as_dict=1, ) if result else {}, ) get_amount = compose( lambda x: x.get("amount", 0), excepts(StopIteration, first, lambda _: {}), lambda col, key: col.get(key, []), ) earnings = get_amounts("earnings") deductions = get_amounts("deductions") def add_remarks(row): start_date = row.get("start_date") return merge( row, {"remarks": "{} SAL".format(start_date.strftime("%b").upper())} ) def set_amounts(row): salary_slip = row.get("salary_slip") amount = get_amount(earnings, salary_slip) - get_amount(deductions, salary_slip) return merge(row, {"amount": amount}) make_row = compose(partial(pick, keys), add_remarks, set_amounts) return with_report_generation_time([make_row(x) for x in result], keys)
def _get_data(clauses, values, keys): items, dates = _query(clauses, values) def add_collection_date(row): def get_collection_date(x): if x.sales_status == "Achieved": return None if x.own_delivery or x.is_return: # x.s_return because return delivery note is always considered to have # the same posting_date as that of the return sales invoice return x.invoice_date return dates.get(x.invoice_name) return merge(row, {"collection_date": get_collection_date(row)}) def add_payment_remarks(items): payments = _get_payments(items) def fn(row): make_remark = compose( lambda x: ", ".join(x), partial(map, lambda x: "{mop}: {amount}".format(mop=x[0], amount=x[1])), lambda x: x.items(), lambda lines: reduceby( "mode_of_payment", lambda a, x: a + get("paid_amount", x, 0), lines, 0, ), lambda x: concatv( get(x.invoice_name, payments, []), get(x.order_name, payments, []) ), frappe._dict, ) return merge(row, {"remarks": make_remark(row)}) return fn def set_null(k, v): if v: return k, v if k not in [ "valuation_rate", "selling_rate", "rate", "qty", "valuation_amount", "amount_before_discount", "discount_amount", "discount_percentage", "amount_after_discount", "ms1", "ms2", "commission_amount", "total", "additional_discount_amount", "total_taxes_and_charges", "grand_total", ]: return k, None return k, 0 def remove_duplicates(columns): invoices = [] def fn(row): invoice_name = row.get("invoice_name") if invoice_name not in invoices: invoices.append(invoice_name) else: return merge(row, {x: None for x in columns}) return row return fn template = reduce(lambda a, x: merge(a, {x: None}), keys, {}) make_row = compose( remove_duplicates( [ "total", "additional_discount_amount", "total_taxes_and_charges", "grand_total", ] ), partial(pick, keys), partial(itemmap, lambda x: set_null(*x)), partial(merge, template), add_payment_remarks(items), add_collection_date, ) return with_report_generation_time([make_row(x) for x in items], keys)
def _get_data(clauses, values, keys): items = frappe.db.sql( """ SELECT s.posting_date AS posting_date, SUM(si.base_net_total) AS net_total, SUM(si.base_total_taxes_and_charges) AS tax_total, SUM(si.base_grand_total) AS grand_total, SUM(sr.base_grand_total) AS returns_grand_total FROM `tabSales Invoice` as s LEFT JOIN ( SELECT * FROM `tabSales Invoice` WHERE is_return = 0 ) AS si ON si.name = s.name LEFT JOIN ( SELECT * from `tabSales Invoice` WHERE is_return = 1 ) AS sr ON sr.name = s.name WHERE {clauses} GROUP BY s.posting_date """.format(clauses=clauses), values=values, as_dict=1, ) si_payments = frappe.db.sql( """ SELECT s.posting_date AS posting_date, p.mode_of_payment AS mode_of_payment, SUM(p.base_amount) AS amount FROM `tabSales Invoice` as s LEFT JOIN `tabSales Invoice Payment` AS p ON p.parent = s.name WHERE {clauses} GROUP BY s.posting_date, p.mode_of_payment """.format(clauses=clauses), values=values, as_dict=1, ) pe_payments = frappe.db.sql( """ SELECT s.posting_date AS posting_date, s.mode_of_payment AS mode_of_payment, IFNULL(SUM(pr.paid_amount), 0) - IFNULL(SUM(pp.paid_amount), 0) AS amount FROM `tabPayment Entry` AS s LEFT JOIN ( SELECT * FROM `tabPayment Entry` WHERE payment_type = 'Pay' ) AS pp ON pp.name = s.name LEFT JOIN ( SELECT * from `tabPayment Entry` WHERE payment_type = 'Receive' ) AS pr ON pr.name = s.name WHERE s.party_type = 'Customer' AND {clauses} GROUP BY s.posting_date, s.mode_of_payment """.format(clauses=clauses), values=values, as_dict=1, ) template = reduce(lambda a, x: merge(a, {x: None}), keys, {}) make_row = compose( partial(valmap, lambda x: x or None), partial(pick, keys), partial(merge, template), _set_payments(si_payments + pe_payments), ) return with_report_generation_time([make_row(x) for x in items], keys)