SELECT
`tabGL Entry`.`posting_date` AS `posting_date`, -- 1
`tabGL Entry`.`voucher_type` AS `voucher_type`, -- 2
`tabGL Entry`.`voucher_no` AS `voucher_no`, -- 3
`tabGL Entry`.`account` AS `account`, -- 4
`tabGL Entry`.`party` AS `party`, -- 5
`tabGL Entry`.`against` AS `against`, -- 6
`tabSales Invoice Item`.`item_name` AS `sales_item`, -- 7
LPAD(FORMAT(`tabSales Invoice Item`.`qty`, 2), 15, ' ') AS `sales_qty`, -- 8 (right-aligned)
FORMAT(`tabSales Invoice Item`.`rate`, 2) AS `sales_rate`, -- 9
LPAD(FORMAT(`tabGL Entry`.`debit`, 2), 15, ' ') AS `debit`, -- 13 (right-aligned)
LPAD(FORMAT(`tabGL Entry`.`credit`, 2), 15, ' ') AS `credit`, -- 14 (right-aligned)
LPAD(
FORMAT(
SUM(`tabGL Entry`.`debit` - `tabGL Entry`.`credit`) OVER (
PARTITION BY `tabGL Entry`.`account`, `tabGL Entry`.`party`
ORDER BY `tabGL Entry`.`posting_date`, `tabGL Entry`.`voucher_no`, `tabGL Entry`.`name`
),
2
),
15,
' '
) AS `balance` -- 15 (right-aligned)
FROM
`tabGL Entry`
LEFT JOIN `tabSales Invoice Item` ON `tabGL Entry`.`voucher_no` = `tabSales Invoice Item`.`parent`
WHERE
`tabGL Entry`.`posting_date` BETWEEN %(start_date)s AND %(end_date)s
AND `tabGL Entry`.`account` LIKE %(account)s
AND `tabGL Entry`.`company` = %(company)s
AND `tabGL Entry`.`party_type` = %(party_type)s
AND `tabGL Entry`.`party` = %(party)s
AND `tabGL Entry`.`is_cancelled` = 0
ORDER BY
`tabGL Entry`.`posting_date` ASC,
`tabGL Entry`.`voucher_no` ASC,
`tabGL Entry`.`name` ASC;
--------------------------------------------------
No.
Label
Fieldtype
Fieldname
Mandatory
Options
1
start_date
Date
start_date
2
end_date
Date
end_date
3
company
Link
company
Company
4
Account
Link
account
Account
5
party_type
Link
party_type
Party Type
6
Party
Dynamic Link
party
party_type
Comments