Report scripts help

You can create a csv file in your Python, but note that this is server-sided. You will need to add a trigger on the client side to execute the function and then grab the csv.

You can have a look at the concept here (it is a page instead of a report, but works accordingly): erpnextswiss/payment_export.js at master · libracore/erpnextswiss · GitHub

First add a menu function for your server-side function (JS), then add the code to generate the output (PY), then use a download function to download the generated file (JS).

Hope this helps.

2 Likes

thanks for reply,

when i create csv file, how to csv file generate in web browser?not in server.

thanks you…

Hi welcome to ERPNext noffri_hendri

Rather than give a screenshot please copy and paste your code here.

That makes this easier for all to discover and try for themselves, thanks

hi clarkej thanks for replay,

my code

def download():

import re
import csv
import os
import sys
import datetime

csv.register_dialect('unixpwd', quoting=csv.QUOTE_ALL)
filename = datetime.datetime.now().strftime("report-develop-%Y-%m-%d-%H-%M.csv")
with open(filename, 'w+') as f:
	writer = csv.writer(f, 'unixpwd')
	writer.writerow(["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"])
	writer.writerow(["LT", "NPWP", "NAMA", "JALAN", "BLOK", "NOMOR", "RT", "RW", "KECAMATAN", "KELURAHAN", "KABUPATEN", "PROPINSI", "KODE_POS", "NOMOR_TELEPON"])
	writer.writerow(["OF", "KODE_OBJEK", "NAMA", "HARGA_SATUAN", "JUMLAH_BARANG","HARGA_TOTAL", "DISKON", "DPP", "PPN", "TARIF_PPNBM", "PPNBM"])
	parents = frappe.db.sql("select MONTH(posting_date) as month,Year(posting_date) as year, posting_date, tax_id, customer, address_display, name from `tabSales Invoice`", as_dict=1)
	for parent in parents:
		writer.writerow(
			(["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']]))
		children = frappe.db.sql(
			"select * from `tabSales Invoice Item` where parent=%s", parent.name, as_dict=1)
		for child in children:
			writer.writerow(["OF","", child['item_name'], child['rate'], child['qty'], child['amount'],"discount"]) 

how csv download in local, not in server?

thanks

Hi @noffri_hendri,

first, on your server end, put the code to create the csv content in a function and make sure to whitelist it.

PY

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

Then, on your client side script add your function and include a download function. Something like this:

JS

function get_csv(frm) {
    frappe.call({
      method: 'myapp.myapp.get_my_csv',
      args: {   },
      callback: function(r) { 
          download("report.csv", r.message.content);
      }
    })
}

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);
}

Hope this helps.

hi @lasalesi thanks for reply,

i runing my code, but not respon…

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",
			callback: function () {
				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);
		}
	})
}

}

code PY:

@frappe.whitelist()
def download():
import re
import csv
import os
import sys
import datetime

csv.register_dialect('unixpwd', quoting=csv.QUOTE_ALL)
filename = datetime.datetime.now().strftime("report-develop-%Y-%m-%d-%H-%M.csv")
# filename = os.path.join('..', path)
with open(filename, 'w+') as f:
	writer = csv.writer(f, 'unixpwd')
	writer.writerow(["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"])
	writer.writerow(["LT", "NPWP", "NAMA", "JALAN", "BLOK", "NOMOR", "RT", "RW", "KECAMATAN", "KELURAHAN", "KABUPATEN", "PROPINSI", "KODE_POS", "NOMOR_TELEPON"])
	writer.writerow(["OF", "KODE_OBJEK", "NAMA", "HARGA_SATUAN", "JUMLAH_BARANG","HARGA_TOTAL", "DISKON", "DPP", "PPN", "TARIF_PPNBM", "PPNBM"])
	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:
		writer.writerow(["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']])
		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:
			writer.writerow(["OF", "", child['item_name'], child['price_list_rate'], child['qty'], child['amount'], child['net_amount'], child['dpp'], child['dpp']*0.1])

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

thanks

In your function, you are still writing a server file. My code snippet however collects the content of the csv as a string. You can build your csv string using

content += ";".join([..])

Then, return the content string from your download function.

If there is an issue in the js execution, monitor your browser console and maybe include some console.log("..."); to see in case code is not executed…

hi @lasalesi thanks for replay,

yes my code still writing in server…

Where I put this code in my code?
content += “;”.join([…])

This will replace the csv writer, so it replaces the writer.writerow. Make sure to append “\n\r” at the end of the line (otherwise your csv becomes one long list of columns).

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 …??