https://github.com/t1m0n/air-datepicker Docs : http://t1m0n.name/air-datepicker/docs/
include in html template
<meta name="pdfkit-orientation" content="Landscape" />
- 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/pypdf#355
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()
- Create a watermark pdf with transparent background using LibreWriter set page transparency to 100 add watermark from Format > Watermark
- 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
- 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"
# 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)
<div style="page-break-after:always;">
<table class="table table-bordered">
<tbody>
</tbody>
</table>
</div>
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"
)
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
frappe.db.sql("select name from tabCustomer", as_dict=True)
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",))
frappe.db.sql("select name from tabCustomer where name like %(name)s and owner=%(owner)s",
{"name": "a%", "owner":"test@example.com"})
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)
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)
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")
SELECT '2021-01-01' + INTERVAL seq DAY `date`
from seq_0_to_365
# If ( NOT (EndA <= StartB or StartA >= EndB) ; “Overlap”)
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,
)
frappe.db._cursor.description
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"))
from_date, to_date = '2020-01-01','2020-12-01'
pandas.date_range(from_date,to_date,freq='m')
def _hash(text):
return hashlib.sha224(text.encode("utf-8")).hexdigest()
df["hash"] = df.map(lambda x: _hash(x["description"]))
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)
- 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 = frappe.ui.color.get('blue', 'extra-light');
python -m compileall apps/my_app/my_app
or
python -m py_compile apps/my_app/my_app/hooks.py
https://www.webforefront.com/django/usebuiltinjinjafilters.html
{% if variable is defined %}
$(document).on("startup", function () {
// custom logic
frappe.set_route("#custom_page");
});
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 });
});
//
}
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;
});
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)
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);
}
});
},
});
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 %}
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);
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);
},
$.map($(".block-module-check"), (t) => {
cur_frm.add_child("block_modules", {"module": t.dataset.module});
})
or
frappe-bench/env/bin/pip install -r apps/your_app/requirement.txt
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();
},
});
},
};
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",
},
};
});
},
});
frappe.ui.form.on("Delivery Note", "refresh", function(frm) {
cur_frm.fields_dict['items'].grid.get_field('item_code').get_query = function(doc, cdt, cdn) {
return {
filters:[
['Item', 'item_name', '=', 'test 1'],
]
}
}
});
pip install jupyterlab
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()
sample:
- https://discuss.erpnext.com/t/customization-in-new-customer-quick-entry-form/46603/13
- erpnext/erpnext/public/js/utils/item_quick_entry.js
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();
},
});
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
$(document).on('startup', function() {
$(document).on('app_ready', function() {
$(document).on('ready toolbar_setup', () =>
frappe.route_hooks.after_load = (frm) => {
frappe.web_form.events.on("after_load", function () {
frappe.logger().debug("some debug message")
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 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;
}
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
);
});
}
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);
}
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)
}
});
https://github.com/DigiThinkIT/frappe_utils/blob/master/monkey.py
def patch_method(obj, method, override):
"""Monkey Patch helper. Will override an object's method with a custome one"""
orig = getattr(obj, method)
if hasattr(orig, "monkeypatched") and orig.monkeypatched == override:
return
override.patched_method = orig
def __fn(*args, **kwargs):
return override(*args, **kwargs)
__fn.monkeypatched = override
setattr(obj, method, __fn)
SELECT * from
(
SELECT table_name AS "Table",
ROUND(((data_length + index_length) / 1024 / 1024 / 1024), 2) AS "Size (GB)"
FROM information_schema.TABLES
WHERE table_schema = "db-name"
) t
WHERE t.`Size (GB)` > .01
ORDER BY (t.`Size (GB)`) DESC;
alter table `tabVersion` Engine=InnoDB
def _set_images(workbook, worksheet="Sheet1"):
"""
Iterate over cells and set image if cell value starts with /files/ or https://
and extension in .jpg, .jpeg, .png
"""
ws = workbook.get_sheet_by_name(worksheet)
for row in ws.iter_rows(min_row=1, max_col=ws.max_column, max_row=ws.max_row):
for cell in row:
if cell.value and (
cstr(cell.value).startswith("https://")
or cstr(cell.value).startswith("/files/")
):
_, extension = os.path.splitext(cell.value)
if extension.lower() in [".png", ".jpg", ".jpeg"]:
try:
content = None
if cell.value.startswith("https://"):
content = requests.get(cell.value).content
else:
item_file = frappe.get_doc("File", {"file_url": cell.value})
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, cell.coordinate)
ws.row_dimensions[cell.row].height = 100
except Exception as e:
print(e)
pass
To Disable Total Row for particular column in frappe report.
On Columns definition add disale_total should be true.