Iāve been working on a solution to this problem for a while for my company. I have a solution that is working, it is tailored specifically to my companies needs and has a hard coded value to identify checks being made out to our āmiscellaneousā vendor, which we use for one off checks like an employee reimbursement etc. So itās not going to plug and play into someone elseās company without some tweaking, but I was able to coble it together primarily with examples and snippets I found on the forum so I wanted to put it out there in case it would help someone else out.
I am currently running this as a script report on the āpurchase invoiceā doctype. Here is a screen shot of the report setup:
The choices I made were to make it a standard report so it can be searched in the awesome bar by itās name which is ācheck runā and so Frappe will generate python, javascript and a json file which is where I can put my custom code.
Note that in order to create a standard report you must be logged in as the default Administator account and you must be in developer mode.
The files get generated into the directory: erpnext/erpnext/accounts/report/check_run as the report is placed into the accounts module per my settings.
Here is my check_run.json file: This is exactly how it was generated I made no alterations.
{
"add_total_row": 0,
"columns": [],
"creation": "2022-08-03 16:11:52.665811",
"disable_prepared_report": 0,
"disabled": 0,
"docstatus": 0,
"doctype": "Report",
"filters": [],
"idx": 0,
"is_standard": "Yes",
"letter_head": "EWP",
"modified": "2022-08-03 22:38:48.019653",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Check Run",
"owner": "Administrator",
"prepared_report": 0,
"ref_doctype": "Purchase Invoice",
"report_name": "Check Run",
"report_type": "Script Report",
"roles": [
{
"role": "Accounts Manager"
},
{
"role": "Accounts User"
}
]
}
Here is my check_run.py file:
import frappe
from frappe import _
from frappe.model.meta import get_field_precision
from frappe.utils import cstr, flt
# this query grabs the data my payables department needs to see along with some data that I
# need for sorting etc. In a script report you must return both a data and a columns object at a
# minimum from a function called execute If you wanted to allow the user to further filter their
# results you could pass those filters to execute and use them to craft a where clause in your
# SQL. The data is a dict type and the columns are an array.
def execute(filters=None):
data = frappe.db.sql("SELECT pi.name, pi.grand_total, pi.bill_no, pi.due_date, ps.payment_term, pi.supplier_name, pi.supplier, pi.supplier_address, pi.status, pi.posting_date FROM `tabPurchase Invoice` pi JOIN `tabPayment Schedule` ps ON pi.name = ps.parent WHERE pi.status in ('Unpaid','Overdue','Partly Paid') OR (pi.status = 'Return' AND pi.outstanding_amount <> 0) ORDER BY pi.posting_date", as_dict=1)
columns = [
{
"fieldname": "name",
"label": _("Purchase Invoice"),
"fieldtype": "Link",
"options": "Purchase Invoice"
},
{
"fieldname": "sage_vend_no",
"label": _("Sage ID"),
"fieldtype": "Data",
"width":100
},
{
"fieldname": "supplier_name",
"label": _("Vendor"),
"fieldtype": "Data",
"width":225
},
{
"fieldname": "bill_no",
"label": _("Vendor Invoice"),
"fieldtype": "Data",
"width":170
},
{
"fieldname": "grand_total",
"label": _("Total"),
"fieldtype": "Currency",
"width": 110
},
{
"fieldname": "due_date",
"label": _("Due Date"),
"fieldtype": "Date"
},
{
"fieldname": "payment_term",
"label": _("Terms"),
"fieldtype": "Data",
"width":120
},
{
"fieldname": "status",
"label": _("Status"),
"fieldtype": "Data",
"width": 90
},
{
"fieldname": "posting_date",
"label": _("Posted Date"),
"fieldtype": "Date"
}
]
return columns, data
and here is my check_run.js file: (Iāve added some comments to try to explain my choices)
frappe.query_reports['Check Run'] = {
get_datatable_options(options) {
return Object.assign(options, {
checkboxColumn: true // add a checkbox column to the the report rows
});
},
onload: function(report) {
// inner message will display the current sum of all grand totals of rows with checked checkboxes
let selectedTotal = report.page.add_inner_message('Selected Total: $0.00').css({"font-size": "17px", "font-weight": "bold", "margin-right": "10px"});
$(".page-wrapper").on('change', '.dt-cell__content > :checkbox', function() {
setTimeout(function(){
getValueOfChecked(); // recalculate selected total on every change to a checkbox
},0);
});
function getValueOfChecked(){
// report.datatable.rowmanager.getCheckedRows is handy built in function to iterate over checked rows
var checkedItemsIndex = report.datatable.rowmanager.getCheckedRows();
let checkedItems = checkedItemsIndex.map(i => report.data[i]);
var total = 0;
$.each(checkedItems, function(index, item) {
total += item.grand_total;
});
$('.inner-page-message').text('Selected Total: $' + total.toFixed(2)); // update UI
}
/*
// this is a method that I built to help my payables coworkers narrow down the visible purchase invoices by only including ones with a due date within a user chosen number of days. My company is not currently using it, hence it is commented out, and I can't vouch for how well it works, but if you need similar functionality it could be a good place to start from.
report.page.add_menu_item(__("Pick Auto Pay"), function() {
var me = this;
var dialog = new frappe.ui.Dialog({
title: __("Select invoices to pay"),
fields: [
{"fieldtype": "Int", "label": __("due in days"), "fieldname": "due_day_number"},
{"fieldtype": "Button", "label": __("get invoices"), "fieldname": "get_invoices", "cssClass": "btn-primary"},
]
});
dialog.fields_dict.get_invoices.$input.click(function() {
var args = dialog.get_values();
dialog.hide();
frappe.call({
method: "getDueInvoices",
args: {
"due_day": frappe.datetime.add_days(new Date(), args.due_day_number)
},
callback: function(data) {
if(data.exc) {
msgprint(__("There were errors."));
}
else {
var invoices = [...new Set(data.message)];
invoices = invoices.join(",");
if(invoices === ''){
invoices = '0';
}
frappe.route_options = {name: ["in", invoices]};
frappe.set_route("query-report","test check");
}
}
});
});
dialog.show();
});*/
/*
This function is where the bulk of the work takes place. It adds a button to the ... menu on the report page called Auto Pay. Which brings up a dialog where the user can enter the beginning check number for the run, the check date, and a button to trigger the creation of payment entries for the checked invoice rows. The beginning check number is used as the reference number on the payment entry and the check date is used as the reference date. I grab those values in my print format for payment entry which corresponds to my pre-printed check layout.
There is some rather confusing code in the make_payment_entry click handler which loops the selected purchase invoice data rows. The first loop generates a struct which is keyed by supplier ID concatenated with their addressLine1 and creates an array for invoices for each supplier. Because we will print only one check per supplier with potentially multiple invoices on it. I do a conditional check for a specific supplier ID 'SUP-2021-00635' which in my company is a miscillaneous vendor. We don't want to put all invoices for misc suppliers on the same check because they are going to separate people. But I can reasonably assume we can combine invoices on to a single check if two or more with the misc supplier are going to the same address.
I then loop through each of the arrays in the struct I previously generated and do a couple of things. I sum up the amount of each invoice to get a total for the check, and I make sure that the number of invoices in the array is not too great to fit onto the payment stub portion of my preprinted checks. In my case I don't want more than 10 invoices to print onto any given check, so I skip a check number and mark it as voided in my custom doctype 'check numbers' which is just a doctype with checknumbers and a note if it's voided or not. This code is very specific to my situation.
Next I generate payment entries for every supplier with references to all purchase invoices included in the run and add the name of each payment entry to a list. After all entries are generated I navigate to the payment entry list with a filter set using the 'in' operator and the list of newly generated payment entries as the value.
This allows the user to view all the new payment entries in draft mode and make any revisions or cancellations as necessary. Then they can select all payment entry checkboxes and use the action menu to submit them and then to print them. They can print them all to a single PDF with one click.
*/
report.page.add_menu_item(__("Auto Pay"), function() {
var me = this;
frappe.call({
method: "getNextCheckNumber",
callback: function(data) {
var dialog = new frappe.ui.Dialog({
title: __("Create Payment Entries for selected invoices"),
fields: [
{"fieldtype": "Int", "label": __("from check number"), "fieldname": "check_number", "default": data.message},
{"fieldtype": "Date", "label": __("check date"), "fieldname": "check_date","reqd":1,"default": frappe.datetime.get_today()},
{"fieldtype": "Button", "label": __("create Payments"), "fieldname": "make_payment_entry", "cssClass": "btn-primary"},
]
});
dialog.fields_dict.make_payment_entry.$input.click(function() {
var args = dialog.get_values();
dialog.hide();
args.names = [];
var checkedItemsIndex = report.datatable.rowmanager.getCheckedRows();
let checkedItems = checkedItemsIndex.map(i => report.data[i]);
var supplierInvoices = {};
var supplierAddress = '';
$.each(checkedItems, function(index, item) {
if(supplierInvoices[item.supplier] == 'SUP-2021-00635'){
supplierAddress = item.supplier_address.replace(/[^a-z0-9]/gi, '');
if(supplierInvoices[item.supplier + supplierAddress] === undefined){
supplierInvoices[item.supplier + supplierAddress] = [{item:item.name,total:item.grand_total}];
}
else{
supplierInvoices[item.supplier + supplierAddress].push({item:item.name,total:item.grand_total});
}
}
else{
if(supplierInvoices[item.supplier] === undefined){
supplierInvoices[item.supplier] = [{item:item.name,total:item.grand_total}];
}
else{
supplierInvoices[item.supplier].push({item:item.name,total:item.grand_total});
}
}
});
var eachcount=0;
$.each(supplierInvoices,function(supplier, invoices){
var numVoidedChecks = parseInt(invoices.length / 10);
var references = [];
var totalCharge = 0;
var discountAmount = 0;
$.each(invoices, function(index, invoice){
frappe.call({
method: "erpnext.accounts.doctype.payment_entry.payment_entry.get_payment_entry",
args: {
"dn": invoice.item,
"dt": report.report_doc.ref_doctype
},
freeze: true,
async: false
}).then(r =>{
var cur_doc = frappe.model.sync(r.message);
cur_doc = cur_doc[0];
if(cur_doc.deductions.length){
discountAmount += cur_doc.deductions[0].amount;
}
});
references.push({reference_doctype:'Purchase Invoice',reference_name:invoice.item,allocated_amount:invoice.total});
totalCharge += invoice.total;
});
for(var i=0; i<numVoidedChecks; i++){
frappe.db.insert({
doctype: 'Check Numbers',
check_number: args.check_number + i,
note:'too many invoices. 1 invoice number for tracking: ' + invoices[0].item,
voided:1
});
}
args.check_number += numVoidedChecks;
frappe.call({
method: "erpnext.accounts.doctype.payment_entry.payment_entry.get_payment_entry",
args: {
"dn": invoices[0].item,
"dt": report.report_doc.ref_doctype
},
freeze: true,
async: false
}).then(r =>{
if (!r.exc){
var cur_doc = frappe.model.sync(r.message);
cur_doc = cur_doc[0];
cur_doc.reference_no= args.check_number++;
cur_doc.reference_date=args.check_date;
cur_doc.posting_date=args.check_date;
cur_doc.mode_of_payment = 'Check';
cur_doc.number_voided_checks = numVoidedChecks;
cur_doc.references = references;
cur_doc.paid_amount = totalCharge + discountAmount;
if(cur_doc.deductions.length){
cur_doc.deductions[0].amount = discountAmount;
}
frappe.call({
method: "frappe.desk.form.save.savedocs",
args: { doc: cur_doc, action: "Save"},
freeze: true
}).then(r =>{
args.names.push(r.docs[0].name);
eachcount++;
frappe.db.insert({
doctype: 'Check Numbers',
check_number: args.check_number - 1,
payment_entry: r.docs[0].name
});
if (eachcount>= args.names.length){
frappe.msgprint(__("Created " + args.names.length + " Payment entries"));
frappe.route_options = {name: ["in",args.names.join(",")]};
frappe.set_route("List","Payment Entry");
}
});
}
});
});
});
dialog.show();
}
});
});
}
}
I have a server script saved as an API type to get my next check number:
The name of the function is getNextCheckNumber and the code is:
number = frappe.db.sql("""
select
Max(check_number)+1
from
`tabCheck Numbers`
""")
if number:
frappe.response['message'] = number[0][0]
I hope this is of some value to someone else out there trying to simplify the process of generating checks.
