Report scripts help

hi @lasalesi ,

code JS:
frappe.query_reports[“Sales invoice script”] = {
“filters”: [
],
onload: function (report) {
report.page.add_inner_button(__(‘Download to CSV’), function () {
return frappe.call({
method: “erpnext.accounts.report.sales_invoice_script.sales_invoice_script.get_my_csv”,
args: {},
callback: function (r) {
download(“report.csv”, r.message.content);
frappe.msgprint(“CSV Berhasil Di Download”);
}
});
function download(filename, content) {
var element = document.createElement(‘a’);
element.setAttribute(‘href’, ‘data:application/octet-stream;charset=utf-8,’ + encodeURIComponent(content));
element.setAttribute(‘download’, filename);

			element.style.display = 'none';
			document.body.appendChild(element);

			element.click();

			document.body.removeChild(element);
		}
	})
}

}

my code running in local, but content file csv local null, why?

image

Try to modify your server-side code to be

@frappe.whitelist()
def get_my_csv():
    content = "my csv content"
    return {'content': content}

just to verify if your file content is “my csv content”. If it is, assign again your function to the content string. For some reason, this is probably empty.

content = ";".join(["FK", "KD_JENIS_TRANSAKSI", "FG_PENGGANTI", "NOMOR_FAKTUR", "MASA_PAJAK", "TAHUN_PAJAK", "TANGGAL_FAKTUR", "NPWP", "NAMA", "ALAMAT_LENGKAP",
           "JUMLAH_DPP", "JUMLAH_PPN", "JUMLAH_PPNBM", "ID_KETERANGAN_TAMBAHAN", "FG_UANG_MUKA", "UANG_MUKA_DPP", "UANG_MUKA_PPN", "UANG_MUKA_PPNBM", "REFERENSI"]) + "\n\r"
for parent in parents:
	content += ";".join(["FK", "1", "", "0", parent['month'], parent['year'], parent['posting_date'], parent['tax_id'], parent['customer'], parent['address_display'], "jlmh dpp", "jlmh ppn", "0", "0", "0", "0", "0", parent['name']])) + "\n\r"
	children = frappe.db.sql(
		"select * from `tabSales Invoice Item` where parent=%s", parent.name, as_dict=1)
	for child in children:
		content += ";".join(["OF","", child['item_name'], child['rate'], child['qty'], child['amount'],"discount"])  + "\n\r"
frappe.log_error( content, "my report content" )   # after execution, check your error log for the content, remove after debug
return content

hi @lasalesi,

i have write your code.:

@frappe.whitelist()
def download():

content = ";".join(map(str, (["FK", "KD_JENIS_TRANSAKSI", "FG_PENGGANTI", "NOMOR_FAKTUR", "MASA_PAJAK", "TAHUN_PAJAK", "TANGGAL_FAKTUR", "NPWP", "NAMA", "ALAMAT_LENGKAP","JUMLAH_DPP", "JUMLAH_PPN", "JUMLAH_PPNBM", "ID_KETERANGAN_TAMBAHAN", "FG_UANG_MUKA", "UANG_MUKA_DPP", "UANG_MUKA_PPN", "UANG_MUKA_PPNBM", "REFERENSI"]))) + "\n\r"
content = ";".join(map(str, (["LT", "NPWP", "NAMA", "JALAN", "BLOK", "NOMOR", "RT", "RW", "KECAMATAN", "KELURAHAN", "KABUPATEN", "PROPINSI", "KODE_POS", "NOMOR_TELEPON"]))) + "\n\r"
content = ";".join(map(str, (["OF", "KODE_OBJEK", "NAMA", "HARGA_SATUAN", "JUMLAH_BARANG", "HARGA_TOTAL", "DISKON", "DPP", "PPN", "TARIF_PPNBM", "PPNBM"]))) + "\n\r"
parents = frappe.db.sql("select MONTH(posting_date) as month,Year(posting_date) as year, posting_date, tax_id, customer, address_display, name,(select SUM((price_list_rate*qty)-net_amount) as JUMLAH_DPP1 from `tabSales Invoice Item` where `tabSales Invoice Item`.parent=`tabSales Invoice`.name) as JUMLAH_DPP  from `tabSales Invoice`", as_dict=1)
for parent in parents:
	content = ";".join(map(str, (["FK", "1", "", "0", parent['month'], parent['year'], parent['posting_date'], parent['tax_id'], parent['customer'], parent['address_display'], parent['JUMLAH_DPP'], parent['JUMLAH_DPP']*0.1, "0", "", "0", "0", "0", "0", parent['name']]))) + "\n\r"
	children = frappe.db.sql("select item_name, price_list_rate, qty, (price_list_rate*qty) as amount, net_amount, ((price_list_rate*qty)-net_amount) as dpp from `tabSales Invoice Item` where parent=%s", parent.name, as_dict=1)
	for child in children:
		content = ";".join(map(str, (["OF", "", child['item_name'], child['price_list_rate'], child['qty'],child['amount'], child['net_amount'], child['dpp'], child['dpp']*0.1]))) + "\n\r"

return content

@frappe.whitelist()
def get_my_csv():
content = download()
return {‘content’: content }

why csv content only one row? this not looping?

Hi @noffri_hendri,

sorry, that was my typo :frowning:

It should be += in both loops (otherwise it only assigns the last)

@frappe.whitelist()
def download():
    content = ";".join(map(str, (["FK", "KD_JENIS_TRANSAKSI", "FG_PENGGANTI", "NOMOR_FAKTUR", "MASA_PAJAK", "TAHUN_PAJAK", "TANGGAL_FAKTUR", "NPWP", "NAMA", "ALAMAT_LENGKAP","JUMLAH_DPP", "JUMLAH_PPN", "JUMLAH_PPNBM", "ID_KETERANGAN_TAMBAHAN", "FG_UANG_MUKA", "UANG_MUKA_DPP", "UANG_MUKA_PPN", "UANG_MUKA_PPNBM", "REFERENSI"]))) + "\n\r"
    content += ";".join(map(str, (["LT", "NPWP", "NAMA", "JALAN", "BLOK", "NOMOR", "RT", "RW", "KECAMATAN", "KELURAHAN", "KABUPATEN", "PROPINSI", "KODE_POS", "NOMOR_TELEPON"]))) + "\n\r"
    content += ";".join(map(str, (["OF", "KODE_OBJEK", "NAMA", "HARGA_SATUAN", "JUMLAH_BARANG", "HARGA_TOTAL", "DISKON", "DPP", "PPN", "TARIF_PPNBM", "PPNBM"]))) + "\n\r"
    parents = frappe.db.sql("select MONTH(posting_date) as month,Year(posting_date) as year, posting_date, tax_id, customer, address_display, name,(select SUM((price_list_rate*qty)-net_amount) as JUMLAH_DPP1 from `tabSales Invoice Item` where `tabSales Invoice Item`.parent=`tabSales Invoice`.name) as JUMLAH_DPP  from `tabSales Invoice`", as_dict=1)
    for parent in parents:
        content += ";".join(map(str, (["FK", "1", "", "0", parent['month'], parent['year'], parent['posting_date'], parent['tax_id'], parent['customer'], parent['address_display'], parent['JUMLAH_DPP'], parent['JUMLAH_DPP']*0.1, "0", "", "0", "0", "0", "0", parent['name']]))) + "\n\r"
        children = frappe.db.sql("select item_name, price_list_rate, qty, (price_list_rate*qty) as amount, net_amount, ((price_list_rate*qty)-net_amount) as dpp from `tabSales Invoice Item` where parent=%s", parent.name, as_dict=1)
        for child in children:
            content += ";".join(map(str, (["OF", "", child['item_name'], child['price_list_rate'], child['qty'],child['amount'], child['net_amount'], child['dpp'], child['dpp']*0.1]))) + "\n\r"
    return content

hi @lasalesi,
thanks its work,…

Happy to hear that :wink: :+1:

does anyone know how to get this table data after generating report ??

Hi @lasalesi

Thanks for your great work on the forum. For the last couple of days we’ve been trying to figure out how to make custom Script Reports work… particularly for accounts hosted with Frappe where we don’t have Server/Admin access

So far, it appears to be an issue of getting the script in the acceptable format as highlighted here but it seems to still not be working. Could you possibly assist by putting your sample code in a format that works as a custom Script Report ? A working sample is all I need, regardless of how basic it is

Thanks plenty

Hi @wale ,

thanks for your feedback.

I assume that you have a custom app, which is installed in your cloud instance? If so,

  1. in your development system, go to Report List > New and create a new report
  2. set the new report to be standard in your custom app and of type “Script Report”
  3. save the report, this will create the file structure in your app

once you have this, take it from the file system and create the code in the “your report name”.py file in the report folder. You can refer to any similar code, for example from ERPNext itself

or for example from popular custom apps such as ERPNextSwiss:

Hope this helps.

1 Like

Thanks a lot @lasalesi

Actually, I was trying to create a custom (non-standard) script report which apparently has a different format but is not listed anywhere in the documentation!

Thankfully, someone else figured it out and shared the format in the thread I referred to earlier

Cheers and thanks again

Hii

How do I get the values in the same row in server side …??