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)
