ERPNext Foundation ERPNext Cloud User Manual Blog Discuss Frappé* Donate

Report scripts help


#1

how 1 create report if i have 2 table, and i will create report…
example :
header :trans id, trans date, outlet id
detail : item id, item name, item price
header : 0001, 19-01-2019, OUT001
detail : P001, oil a, $20
P002, oil b, $22
header : 0010, 19-01-2019, OUT009
detail : P004, oil d $20
P010, oil f, $22


#2

Hi @noffri_hendri,

not sure if I understand what you are trying to achieve. But I guess what you want can be done with a Query Report. Log in as “Administrator”, open Reports List and create a new report. Then, add your MySQL query, in your case probably something like

SELECT `t1`.`trans_id`, `t1`.`trans_date`, `d1`.`item_id`
FROM `tabTransaction` AS `t1`
JOIN `tabDetail` AS  `d1` ON `t1`.`item_id` = `d1`.`item_id`;

#3

when 1 select table,field in master table and child table no in 1 row, buat different row…

i mean use looping…

image


#4

For this you might need to look into the script report and define a script that generates your data.

Please refer to https://frappe.io/docs/user/en/guides/reports-and-printing/how-to-make-script-reports


#5

yes, but i can’t writing script looping in frappe… can you help

me?


#6

Try something like this:

def execute(filters=None):
    columns, data = [], []
    columns = ["header", "TRASN ID", "TRANS DATE", "OUTLET ID"]
    parents = frappe.db.sql("<your query", as_dict=1)
    for parent in parents:
        data.append([parent['name'], parent['trans_id'], parent['trans_date'], ""])
        children = frappe.db.sql("<your query>", as_dict=1)
        for child in children:
            data.append(["detail", child['item_code'], child['item_name'], ""])

    return columns, data

Hope this helps.


#7

thanks you it’s work, but can i add tittle child columns under header columns?how?
image header columns, how can i add detils columns?

image


#8

The system will not support a second “real” header as far as I know… Entering the rows in the table works the same as entering the data…


To custom report csv, in report script
#9

how if i create to csv? and don’t show in grid columns?

thanks you for helping me…


#10

You can use Menu > Export > Excel and then columns and all grid rows are going to be exported…


#11

thanks you for Reply,

how i export csv in python script?because i will costum columns in csv?


#12

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): https://github.com/libracore/erpnextswiss/blob/master/erpnextswiss/erpnextswiss/page/payment_export/payment_export.js

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.


#13

thanks for reply,

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

thanks you…


#14

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


#15

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


#16

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.


#17

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


#18

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…


#19

hi @lasalesi thanks for replay,

yes my code still writing in server…

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


#20

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).