Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Frappe Reference Guide

Frappe Dev Hacks

Frappe Datepicker control

https://github.com/t1m0n/air-datepicker Docs : http://t1m0n.name/air-datepicker/docs/

Pdf landscape orientation

include in html template

<meta name="pdfkit-orientation" content="Landscape" />

Pdf Fillable form issues

  • Form Fields do not appear unless focussed in some pdf viewers
  • issue due to NeedAppearances
  • normal merge/writing with PyPdf2.PdfFileMerger, PyPdf2.PdfFileWriter or pdfrw.PdfWriter cannot solve the issue
  • solution is to use init_pdf_writer_from_reader as detailed here py-pdf/PyPDF2#355

PyPdf2 PdfFileMerger to merge pdfs

from PyPDF2 import PdfFileMerger, PdfFileReader
merger = PdfFileMerger()
content = get_pdf(frappe.render_template('....',{})
merger.append(PdfFileReader(io.BytesIO(content)))

# save to file path
merger.write("{0}_{1}.pdf".format(self.name, now_datetime()))

# add as attachment
out = io.BytesIO()
merger.write(out)
_file = frappe.get_doc(
   {
       "doctype": "File",
       "file_name": "application_{}.pdf".format(now_datetime()),
       "attached_to_doctype": self.doctype,
       "attached_to_name": self.name,
       "is_private": 1,
       "content": out.getvalue(),
   }
)
_file.save()

Add Watermark to pdf

  1. Create a watermark pdf with transparent background using LibreWriter set page transparency to 100 add watermark from Format > Watermark
  2. create pdf in frappe using one of the ususal methods.. get_pdf
def add_watermark(pdf_file, watermark_pdf=None):
from frappe.email.email_body import get_filecontent_from_path
from frappe.utils.pdf import get_file_data_from_writer

if not watermark_pdf:
watermark_pdf = "watermark_draft.pdf"
path = get_files_path(watermark_pdf, is_private=True)
watermark_pdf = PyPDF2.PdfFileReader(path)

import io
input_pdf = PyPDF2.PdfFileReader(io.BytesIO(pdf_file))

writer = PyPDF2.PdfFileWriter()
page_count = input_pdf.getNumPages()
for page_number in range(page_count):
input_page = input_pdf.getPage(page_number)
input_page.mergePage(watermark_pdf.getPage(0))
writer.addPage(input_page)

filedata = get_file_data_from_writer(writer)

return filedata
  1. https://medium.com/@schedulepython/how-to-watermark-your-pdf-files-with-python-f193fb26656e

Wkhtmltopdf - pdf formatting

  1. frappe/frappe/utils/pdf.py
  • To set custom margins for your pdf - pass margins to the get_pdf method

    get_pdf(html, {
    "margin-left": "0mm",
    "margin-right": "0mm",
    "margin-top": "5mm",
    "margin-bottom": "0mm",
    })
    
  • also to get the top and bottom margins effective, need to include elements with id='header-html' and 'footer-html'. Include blank placeholders, if you do not have a header/footer

    <div id="header-html">
    <!-- empty place holder to remove top-marging in pdf -->
    </div>
    <div id="footer-html">
    <!-- empty place holder to remove bottom-marging in pdf -->
    </div>
    
    
  • full list of Wkhtmltopdf options that may work with get_pdf https://wkhtmltopdf.org/usage/wkhtmltopdf.txt

    e.g.

    frappe.response.filename = filename
    frappe.response.filecontent = get_pdf(html, {
    "margin-top": "5mm",
    "margin-bottom": "5mm",
    "margin-left": "0mm",
    "margin-right": "0mm",
    "page-size": "A3",
    "zoom": "0.65",
    "footer-right": "Page [page] of [toPage]",
    })
    frappe.response.type = "download"
    
    

Fill pdf form

# Uses https://github.com/revolunet/pypdftk for form filling and merging pdf
# pypdftk depends on pdftk
# need to install pypdftk and pdftk
# sudo apt-get install pdftk
# pip install pypdftk

# Use qpdf to remove password if pdf is protected

qpdf –password=password_or_blank_for_empty_password  –decrypt /home/lori/Documents/secured.pdf /home/lori/Documents/unsecured.pdf


# fill 2 templates and merge pdfs
template_a = frappe.get_site_path('private', 'files', 'a.pdf')
template_b = frappe.get_site_path('private', 'files', 'b.pdf')

doc = frappe.get_doc("Assessment", docname)
file_name = "%s.pdf" % doc.name

a = pypdftk.fill_form(
template_a, {"a":"A"}, out_file=touch_random_file())

b = pypdftk.fill_form(
template_b, {"b":"B"}, out_file=touch_random_file())

a_b_merged = pypdftk.concat([a,b], touch_random_file())

with open(merged_file, "rb") as fileobj:
filedata = fileobj.read()
frappe.local.response.filename = file_name
frappe.local.response.filecontent = filedata
frappe.local.response.type = "download"

example below reads form field name and updates value of field with its name.

The name of the field is in /TU property

Uses pdfrw.

pip install pdfrw.

pypdftk above is more reliable as pdfrw does not handle encrypted/compressed pdf

To get form fields:

pdftk foo.pdf dump_data_fields
import pdfrw

template_pdf = pdfrw.PdfFileReader(fname="template.pdf")
template_pdf.Root.AcroForm.update(
pdfrw.PdfDict(NeedAppearances=pdfrw.PdfObject("true"))
)

for page in template_pdf.pages:
annots = page.get("/Annots")
if annots:
for a in annots:
field_desc = a["/TU"]
a.update(pdfrw.PdfDict(V="{}".format(field_desc or "")))

pdfrw.PdfWriter().write("output.pdf", template_pdf)

Fix Wkhtmltopdf Table header overlaps content when table spans across page

<style> thead { display: table-header-group } tfoot { display: table-row-group } tr { page-break-inside: avoid } </style>
<div style="page-break-after:always;">
    <table class="table table-bordered">
      <tbody>
      </tbody>
    </table>
</div>

attach pdf to doc

        out = io.BytesIO()
        .... write pdf to out
        _file = frappe.get_doc(
            {
                "doctype": "File",
                "file_name": "example_{}.pdf".format(now_datetime()),
                "attached_to_doctype": self.doctype,
                "attached_to_name": self.name,
                "is_private": 1,
                "content": out.getvalue(),
            }
        )
        _file.save()
        frappe.db.commit()

Wierd issue when setting datetime field in py.. form is not saved, no error and web_form for the Doctype crashes

Solution: Set datetime field as formatted string

        doc.submission_date = frappe.utils.format_datetime(
            frappe.utils.now(), "yyyy-MM-dd HH:mm:ss"
        )

DB usage

Writing raw Sql using the frappe.db.sql api. Using parameterized queries to handle SQL Injection. Documentation for the built in ORM api can be found here https://frappe.io/docs/user/en/api/database

  • frappe/frappe/database/database.py

  • frappe/frappe/__init__.py

simple query with no parameters

frappe.db.sql("select name from tabCustomer", as_dict=True)

tuple of parameter values, will be escaped and substituted in the query.

frappe.db.sql("select name from tabCustomer where name like %s", "a%")

frappe.db.sql("select * from tabCustomer where creation > %s and company = %s",("2019-01-01","abc",))

parameter values as dict, will be escaped and substituted in the query.

frappe.db.sql("select name from tabCustomer where name like %(name)s and owner=%(owner)s",
{"name": "a%", "owner":"test@example.com"})

using an in clause, (parameters are a list of values)

so_item_rows = list(set([d.so_detail for d in item_list]))

delivery_notes = frappe.db.sql("""
select parent, so_detail
from `tabDelivery Note Item`
where docstatus=1 and so_detail in (%s)
group by so_detail, parent
""" % (', '.join(['%s']*len(so_item_rows))), tuple(so_item_rows), as_dict=1)

in using frappe.get_list (same as above,)

so_item_rows = list(set([d.so_detail for d in item_list]))
delivery_notes = frappe.get_list("Delivery Note Item",fields=("parent","so_detail",), filters={"docstatus":1, "so_detail":("in",so_item_rows)})
course_list = [d["course"] for d in courses]
for d in frappe.get_list("Student Group", fields=("name"), filters={"program": program, "course":("in", course_list), "disabled": 0}):
student_group = frappe.get_doc("Student Group", d.name)

group by in frappe.get_all

fields = [
"company", "count(name) as total_invoices", "sum(outstanding_amount) as outstanding_amount"
]
for doctype in ["Sales Invoice", "Purchase Invoice"]:
invoices = frappe.get_all(doctype, filters=dict(is_opening="Yes", docstatus=1),
fields=fields, group_by="company")

Generate sequence of Dates using Sequence Engine

SELECT '2021-01-01' + INTERVAL seq DAY `date` 
from seq_0_to_365

Check for overlap in 2 date ranges

    # If ( NOT (EndA <= StartB or StartA >= EndB) ; “Overlap”)

MariaDB VALUES clause

    values = ",".join("(%s)" for d in range(len(items)))

    frappe.db.sql(
        """
        with t(item_code)
        as (VALUES {})
        select 
            t.rn, t.item_code
        from t
        where not exists 
        (select 1 from tabItem x where x.item_code = t.item_code )
    """.format(
            values
        ),
        tuple(items),
        debug=1,
    )

Pandas for Pivot

Single Index col

    df = pandas.DataFrame.from_records(data)
    df1 = pandas.pivot_table(
        df,
        index=[
            "fieldname1",
            "fieldname2",
        ],
        values=["agg_fieldname"],
        columns=["col_fieldname"],
        fill_value=0,
        margins=True,
        margins_name="Total",
        aggfunc=sum,
        dropna=True,
    )
    df1.drop(index="Total", axis=0)
    df1.columns = df1.columns.to_series().str[1]
    df2 = df1.reset_index()

    columns = [
        dict(label="Field 1", fieldname="fieldname1", fieldtype="Data", width=100),
        dict(label="Field 2", fieldname="fieldname2", fieldtype="Data", width=100),
    ]

    for col in df1.columns.to_list():
        columns += [
            dict(label=col, fieldname=col, fieldtype="Currency", width=100),
        ]

    return columns, df2.to_dict("r")

Aggregate on multiple columns

sales_order_list = frappe.db.sql("""
    select 
        customer, transaction_date, base_net_total, 
        discount_amount, advance_paid
    from `tabSales Order`""", as_dict=True)
df = pandas.DataFrame.from_records(sales_order_list)
df1 = pandas.pivot_table(
  df,
  index=["customer", "transaction_date",],
  values=[
    "base_net_total",
    "discount_amount",
    "advance_paid",
  ],
  fill_value="",
  aggfunc=sum,
  dropna=True,
)
df2 = df1.reset_index()
data = df2.to_dict("r")

Simple groupby single column

        df = pandas.DataFrame.from_records(data)
        df1 = df[["item_code", "qty"]]
        g = df1.groupby("item_code", as_index=False).agg("sum")
        data = g.to_dict("r")
        data = sorted(data, key=itemgetter("item_code"))

Pandas generate dates in range

from_date, to_date = '2020-01-01','2020-12-01'
pandas.date_range(from_date,to_date,freq='m')

Pandas dataframe Mapped column from existing column

def _hash(text):
    return hashlib.sha224(text.encode("utf-8")).hexdigest()

df["hash"] = df.map(lambda x: _hash(x["description"]))

Ageing column query - case

def get_ageing(filters, age_column):
    ageing = ["case", "else '{} +' end".format(filters.get("range3"))]
    low = 0
    for d in ["range1", "range2", "range3"]:
        days = filters.get(d)
        ageing.insert(
            -1,
            "when `{}` > DATE_SUB(%(from_date)s, INTERVAL {} DAY) then '{}-{}'".format(
                age_column, days + 1, low, days
            ),
        )
        low = days + 1
    return " ".join(ageing)

# usage
filters = {"from_date":"2021-02-01","range1" : 15, "range2" : 30, "range3": 60 }
get_ageing(filters)

Excel api

  • create an excel file and save to File
# array of arrays
data_rows=[["sample","data"]]

from frappe.utils.xlsxutils import make_xlsx
xlsx_file = make_xlsx(data_rows, "Sample Data")
file_data = xlsx_file.getvalue()

_file = frappe.get_doc({
"doctype": "File",
"file_name": "Sample Data.xlsx,
"folder": "Home/Attachments",
"content": file_data})
_file.save()

color

color = frappe.ui.color.get('blue', 'extra-light');

compile py files instead of supervisor reload in production

python -m compileall apps/my_app/my_app

or

python -m py_compile apps/my_app/my_app/hooks.py

Jinja built in filters

https://www.webforefront.com/django/usebuiltinjinjafilters.html

{% if variable is defined %}

Js Startup hook to redirect on login

$(document).on("startup", function () {
// custom logic
frappe.set_route("#custom_page");
});

Make Data field in child table to awesomplete

use case - where you need autocomplete/typeahead in child table field which is not a link (doctype). Using a select does not give typeahead and also not practical if list of options is more than a few. In that case set the field to type Data and create awesomplete on focus. Use MutationObserver to observe plug into style change in column field_area. When grid row becomes editable, frappe creates an input for the column and sets class to 'input-sm' on focus. the observer kicks in here and changes the input to awesomplete.

const fieldObserver = new MutationObserver((mutations) => {
mutations.forEach((mu) => {
if (mu.type !== "attributes" && mu.attributeName !== "class") return;
let $select = $(mu.target).find("input")[0];
let aws = new Awesomplete($select, { minChars: 0 });
aws.list = ["foo","bar"]; // can be set after a frappe call
});
});

Start observing the target node for configured mutations

refresh: function (frm) {
//
cur_frm.fields_dict.items.grid.grid_rows.forEach(
(row) => {
let el = row.columns.my_data_field.field_area[0];
fieldObserver.observe(el, { attributes: true });
});
//
}

child table - add row event:

Add a _add callback in .js. e.g.

frappe.ui.form.on(
  "table_field_fieldname",
  "table_field_fieldname_add",
  function (frm) { 
      // let items =  frm.doc.table_field_fieldname;
});

change child table grid row properties

    frm.fields_dict.items.grid.grid_rows[0].toggle_editable("rate", false);
    frm.fields_dict.items.grid.update_docfield_property("rate", "read_only", 0);
    frm.fields_dict.items.grid.toggle_reqd("item_code", frm.doc.update_stock? true: false);
    frm.fields_dict.items.grid.update_docfield_property("remarks","hidden",1)

e.g. make rate read_only for returns..(note: works only if done in grid-row-render )

frappe.ui.form.on("Sales Invoice", {
  setup: function (frm) {
    $(frm.wrapper).on("grid-row-render", function (e, grid_row) {
      if (frm.doc.is_return && grid_row.grid.df.fieldname == "items") {
        grid_row.activate();
        grid_row.toggle_editable("rate", false);
      }
    });
  },
});

Print Format

creating Custom Print Format create a Print Format - type = jinja, standard = Yes

  • add a html file of the same name in the folder created

  • create a LetterHead with necessary Header and Footer

  • to use the standrd header import add_header from standard_macros.html

    {%- from "templates/print_formats/standard_macros.html" import add_header -%}
    {{ add_header(0, 1, doc, letter_head, no_letterhead, print_settings) }}
    

    or copy the header code from standard_macros.html into your own html and modify as needed.

  • to get a footer add this in your html:

{% if print_settings.repeat_header_footer %}
    <div id="footer-html" class="visible-pdf">
    {% if not no_letterhead and footer %}
    <div class="letter-head-footer">
    {{ footer }}
    </div>
    {% endif %}
    <p class="text-center small page-number visible-pdf">
    {{ _("Page {0} of {1}").format('<span class="page"></span>', '<span class="topage"></span>') }}
    </p>
    </div>
    {% endif %}

Override frappe/erpnext js

sample to override pos payment dialog

  // Code for overriding functions if required
  var override = function (object, methodName, callback) {
    object[methodName] = callback(object[methodName]);
  };
  setTimeout(() => {
    override(wrapper.pos, "make_payment_modal", function (original) {
      return function () {
        debugger;
        original.apply(this);
      };
    });
  }, 1000);

Cell formatter in query reports

add a formatter function in the report_name.js file

  formatter(value, row, column, data, format_cell) {
    if (column.fieldname == "sales_invoice_name" && data.sales_invoice_name) {
      return format_cell(value, row, column, data);
    }
    return format_cell(value, row, column, data);
  },

use case where different doctype links in same column

  formatter(value, row, column, data, format_cell) {
    if (column.fieldname == "reference_name" && data.reference_name) {
      let form_link = frappe.utils.get_form_link(
        data.reference_doctype,
        data.reference_name
      );
      return `<a class="text-muted grey" href="${form_link}">${value}</a>`;
    }
    return format_cell(value, row, column, data);
  },

clear all allowed modules in user form

$.map($(".block-module-check"), (t) => {
    cur_frm.add_child("block_modules", {"module": t.dataset.module});
})

requirements

or
frappe-bench/env/bin/pip install -r apps/your_app/requirement.txt

add standard field in Listview filter

create a listview_settings file along with the doctype files.. doctype/some_custom_doctype/some_custom_doctype_list.js

frappe.listview_settings["Some Custom Doctype"] = {
  onload: function (listview) {
    listview.page.add_field({
      fieldtype: "Select",
      label: __("DocStatus"),
      fieldname: "docstatus",
      options: [
        { label: "Draft", value: 0 },
        { label: "Submitted", value: 1 },
        { label: "Cancelled", value: 2 },
      ],
      onchange: function () {
        listview.refresh();
      },
    });
  },
};

filter child table link field, based on row data

e.g. journal entry > accounts > party_type based on row.account

frappe.ui.form.on("Sales Invoice", {
  setup: function (frm) {
    frm.set_query("uom", "items", function (doc, cdt, cdn) {
      let row = locals[cdt][cdn];
      return {
        query:
          "erpnext.accounts.doctype.pricing_rule.pricing_rule.get_item_uoms",
        filters: {
          value: row.item_code,
          apply_on: "Item Code",
        },
      };
    });
  },
});

Jupyer Notebook

install jupyterlab in env

pip install jupyterlab

connect to site, db

import frappe
frappe.init(site='site1.local', sites_path='/home/frappe/frappe-bench/sites')
frappe.connect()
frappe.local.lang = frappe.db.get_default('lang')
frappe.db.connect()

Custom quick_entry form for core doctype

sample:

code below sets changes df property of another field on change of Status field

frappe.provide("frappe.ui.form");

frappe.ui.form.LeadQuickEntryForm = frappe.ui.form.QuickEntryForm.extend({
  render_dialog: function () {
    this._super();
    let dialog = this.dialog;
    let template_field = dialog.get_field("status");
    template_field.df.onchange = function (params) {
      let target = dialog.get_field("contact_date");
      target.df.reqd = dialog.get_values()["status"] == "Lead";
      target.refresh();
    };
    template_field.refresh();
  },
});

email configuration in site_config.json

can be used to quickly setup a default email account. Disable the exisiting default sending email account if any (ususally Notifications).

  "mail_server": "smtp.gmail.com",
  "mail_port": 587,
  "use_tls": 1,
  "mail_login": "...@gmail.com",
  "mail_password": "app password",
  "auto_email_id": "...@gmail.com",
  "email_sender_name": "Notifications",
  "always_use_account_email_id_as_sender": 0,
  "always_use_account_name_as_sender_name": 0

event callbacks in desk,page

$(document).on('startup', function() {

$(document).on('app_ready', function() {

$(document).on('ready toolbar_setup', () =>

frappe.route_hooks.after_load = (frm) => {

event callbacks in web_form

frappe.web_form.events.on("after_load", function () {

Log to frappe.log

frappe.logger().debug("some debug message")

n sized chunks from list

def chunks(lst, n):
    """Yield successive n-sized chunks from lst."""
    for i in range(0, len(lst), n):
        yield lst[i:i + n]

Set number format in query script report

set custom formatter function in .js

	"formatter": function (value, row, column, data, default_formatter) {
		value = default_formatter(value, row, column, data);
		if (column.fieldtype == "Float") {
			if (!data[column.id])
				return "";
			let val = parseFloat(data[column.id]).toFixed(1);
			return `<div style='text-align: right'>${val}</div>`;
		}
		return value;
	}

load paginated data using fetch


  fetch_data(args) {
    let me = this;
    return fetch(
      "/api/method/my_app.get_report",
      {
        method: "POST",
        headers: {
          "X-Frappe-CSRF-Token": frappe.csrf_token,
          "Content-Type": "application/json;charset=utf-8",
        },
        body: JSON.stringify(args),
      }
    )
      .then((response) => {
        if (!response.ok) {
          throw new Error("Network response was not OK");
        }
        return response.json();
      })
      .then((r) => {
        if (r.message && r.message.total_segments) {
          // console.log(r.message)
          frappe.show_alert(`Fetching ${r.message.record_count} records`, 20);
          // this method is used to load several requests in parallel.
          me.segment_count = 0;
          me.total_segments = r.message.total_segments;
          me.record_count = r.message.record_count;

          let promises = [];
          for (let idx = 1; idx <= r.message.total_segments; idx++) {
            let _args = Object.assign({ segment: idx, }, args)
            promises.push(me.papa_fetch(_args));
          }
          Promise.all(promises).then(() => {
            // 
          })
        }
      })
      .catch((error) => {
        console.error(
          "There has been a problem with your fetch operation:",
          error
        );
      });
  }

Add links in document Connections/Dashboard

	refresh: function (frm) {
		$('div').find('.document-link[data-doctype="Purchase Order"]').remove();

		let link = $(`
		<div class="document-link" data-doctype="Purchase Order">
			<div class="document-link-badge" data-doctype="Purchase Order"> <span class="count">1</span> <a
				class="badge-link">Purchase Order</a> </div> <span class="open-notification hidden"
			title="Open Purchase Order"> </span> <button class="btn btn-new btn-secondary btn-xs icon-btn"
			data-doctype="Purchase Order"> <svg class="icon icon-sm">
				<use href="#icon-add"></use>
			</svg> </button>
		</div>
		`);

		link.on('click', function () {
			// frm.dashboard.open_document_list($(this).closest('.document-link'));
			frappe.route_options = { 'name': ['in', 'PUR-ORD-2022-00042'] };
			frappe.set_route("List", "Purchase Order", "List");

		})

		$('div').find('.document-link[data-doctype="Supplier Packing List Art"]').after(link);
	}

add images in excel cell, openpyxl


def add_images(data, workbook, worksheet=""):
    ws = workbook.get_sheet_by_name(worksheet)
    image_col = "S"  # get_column_letter(len(data[0]) - 2)
    for row, image_url in enumerate(data):
        if image_url:
            _filename, extension = os.path.splitext(image_url)
            if extension in [".png", ".jpg", ".jpeg"]:
                try:
                    content = None

                    if image_url.startswith("http"):
                        content = requests.get(image_url).content
                    else:
                        item_file = frappe.get_doc("File", {"file_url": image_url})
                        content = item_file.get_content()
                    if content:
                        image = openpyxl.drawing.image.Image(io.BytesIO(content))
                        image.height = 100
                        image.width = 100
                        ws.add_image(image, f"{image_col}{cstr(row+1)}")
                        ws.row_dimensions[row + 1].height = 90
                except Exception as e:
                    print(e)
                    pass

Read xlsx in browser: SheetJs https://sheetjs.com

new frappe.ui.FileUploader({
	as_dataurl: true,
	allow_multiple: false,
	on_success(file) {
		var reader = new FileReader();
		reader.onload = function (e) {
			var workbook = XLSX.read(e.target.result);
			var csv = XLSX.utils.sheet_to_csv(workbook.Sheets['Supplier Packing List Detail']);
			var data = frappe.utils.csv_to_array(csv);
			console.log(data);

			// add your logic here..
		};
		reader.readAsArrayBuffer(file.file_obj)
	}
});
				```
@lp-at
Copy link

lp-at commented Apr 26, 2021

To Disable Total Row for particular column in frappe report.
On Columns definition add disale_total should be true.

label="Name", fieldname="name", fieldtype="Data", disable_total=True

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment