def download_ticts_loading_list(**args):
args = frappe._dict(args)
data = [['Test 5', 'Test 6', args.args], ['Test 7', 'Test 8']]
sheet_name = "Loading List"
wb = openpyxl.Workbook(write_only=True)
ws = wb.create_sheet(sheet_name, 0)
row1 = ws.row_dimensions[1]
row1.font = Font(name='Calibri', bold=True)
#data = [['Test 5', 'Test 6'], ['Test 7', 'Test 8']]
for row in data:
clean_row = []
for item in row:
if isinstance(item,
basestring) and sheet_name != "Data Import Template":
value = handle_html(item)
else:
value = item
clean_row.append(value)
ws.append(clean_row)
xlsx_file = StringIO()
wb.save(xlsx_file)
frappe.response['filename'] = sheet_name + '.xlsx'
frappe.response['filecontent'] = xlsx_file.getvalue()
frappe.response['type'] = 'download'
def write_xlsx(data, sheet_name, wb=None, column_widths=None, file_path=None): # from xlsx utils with changes column_widths = column_widths or [] if wb is None: wb = openpyxl.Workbook(write_only=True) ws = wb.create_sheet(sheet_name, 0) for i, column_width in enumerate(column_widths): if column_width: ws.column_dimensions[get_column_letter(i + 1)].width = column_width row1 = ws.row_dimensions[1] row1.font = Font(name='Calibri', bold=True) for row in data: clean_row = [] for item in row: if isinstance(item, string_types) and (sheet_name not in ['Data Import Template', 'Data Export']): value = handle_html(item) else: value = item if isinstance(item, string_types) and next(ILLEGAL_CHARACTERS_RE.finditer(value), None): # Remove illegal characters from the string value = re.sub(ILLEGAL_CHARACTERS_RE, '', value) clean_row.append(value) ws.append(clean_row) wb.save(file_path) return True
def export_query():
"""export from report builder"""
form_params = get_form_params()
form_params["limit_page_length"] = None
form_params["as_list"] = True
doctype = form_params.doctype
add_totals_row = None
file_format_type = form_params["file_format_type"]
del form_params["doctype"]
del form_params["file_format_type"]
if 'add_totals_row' in form_params and form_params['add_totals_row'] == '1':
add_totals_row = 1
del form_params["add_totals_row"]
frappe.permissions.can_export(doctype, raise_exception=True)
if 'selected_items' in form_params:
si = json.loads(frappe.form_dict.get('selected_items'))
form_params["filters"] = {"name": ("in", si)}
del form_params["selected_items"]
db_query = DatabaseQuery(doctype)
ret = db_query.execute(**form_params)
if add_totals_row:
ret = append_totals_row(ret)
data = [['Sr'] + get_labels(db_query.fields, doctype)]
for i, row in enumerate(ret):
data.append([i + 1] + list(row))
if file_format_type == "CSV":
# convert to csv
import csv
from frappe.utils.xlsxutils import handle_html
f = StringIO()
writer = csv.writer(f)
for r in data:
# encode only unicode type strings and not int, floats etc.
writer.writerow([handle_html(frappe.as_unicode(v)).encode('utf-8') \
if isinstance(v, string_types) else v for v in r])
f.seek(0)
frappe.response['result'] = text_type(f.read(), 'utf-8')
frappe.response['type'] = 'csv'
frappe.response['doctype'] = doctype
elif file_format_type == "Excel":
from frappe.utils.xlsxutils import make_xlsx
xlsx_file = make_xlsx(data, doctype)
frappe.response['filename'] = doctype + '.xlsx'
frappe.response['filecontent'] = xlsx_file.getvalue()
frappe.response['type'] = 'binary'
def export_query():
"""export from report builder"""
form_params = get_form_params()
form_params["limit_page_length"] = None
form_params["as_list"] = True
doctype = form_params.doctype
add_totals_row = None
file_format_type = form_params["file_format_type"]
del form_params["doctype"]
del form_params["file_format_type"]
if 'add_totals_row' in form_params and form_params['add_totals_row']=='1':
add_totals_row = 1
del form_params["add_totals_row"]
frappe.permissions.can_export(doctype, raise_exception=True)
if 'selected_items' in form_params:
si = json.loads(frappe.form_dict.get('selected_items'))
form_params["filters"] = {"name": ("in", si)}
del form_params["selected_items"]
db_query = DatabaseQuery(doctype)
ret = db_query.execute(**form_params)
if add_totals_row:
ret = append_totals_row(ret)
data = [['Sr'] + get_labels(db_query.fields, doctype)]
for i, row in enumerate(ret):
data.append([i+1] + list(row))
if file_format_type == "CSV":
# convert to csv
import csv
from frappe.utils.xlsxutils import handle_html
f = StringIO()
writer = csv.writer(f)
for r in data:
# encode only unicode type strings and not int, floats etc.
writer.writerow([handle_html(frappe.as_unicode(v)).encode('utf-8') \
if isinstance(v, string_types) else v for v in r])
f.seek(0)
frappe.response['result'] = text_type(f.read(), 'utf-8')
frappe.response['type'] = 'csv'
frappe.response['doctype'] = doctype
elif file_format_type == "Excel":
from frappe.utils.xlsxutils import make_xlsx
xlsx_file = make_xlsx(data, doctype)
frappe.response['filename'] = doctype + '.xlsx'
frappe.response['filecontent'] = xlsx_file.getvalue()
frappe.response['type'] = 'binary'
def get_tax_accounts(item_list,
columns,
company_currency,
doctype="Sales Invoice",
tax_doctype="Sales Taxes and Charges"):
import json
item_row_map = {}
tax_columns = []
invoice_item_row = {}
itemised_tax = {}
tax_amount_precision = get_field_precision(
frappe.get_meta(tax_doctype).get_field("tax_amount"),
currency=company_currency) or 2
for d in item_list:
invoice_item_row.setdefault(d.parent, []).append(d)
item_row_map.setdefault(d.parent,
{}).setdefault(d.item_code or d.item_name,
[]).append(d)
conditions = ""
if doctype == "Purchase Invoice":
conditions = " and category in ('Total', 'Valuation and Total') and base_tax_amount_after_discount_amount != 0"
deducted_tax = get_deducted_taxes()
tax_details = frappe.db.sql(
"""
select
name, parent, description, item_wise_tax_detail,
charge_type, base_tax_amount_after_discount_amount
from `tab%s`
where
parenttype = %s and docstatus = 1
and (description is not null and description != '')
and parent in (%s)
%s
order by description
""" % (tax_doctype, '%s', ', '.join(
['%s'] * len(invoice_item_row)), conditions),
tuple([doctype] + list(invoice_item_row)))
for name, parent, description, item_wise_tax_detail, charge_type, tax_amount in tax_details:
description = handle_html(description)
if description not in tax_columns and tax_amount:
# as description is text editor earlier and markup can break the column convention in reports
tax_columns.append(description)
if item_wise_tax_detail:
try:
item_wise_tax_detail = json.loads(item_wise_tax_detail)
for item_code, tax_data in item_wise_tax_detail.items():
itemised_tax.setdefault(item_code, frappe._dict())
if isinstance(tax_data, list):
tax_rate, tax_amount = tax_data
else:
tax_rate = tax_data
tax_amount = 0
if charge_type == "Actual" and not tax_rate:
tax_rate = "NA"
item_net_amount = sum([
flt(d.base_net_amount) for d in item_row_map.get(
parent, {}).get(item_code, [])
])
for d in item_row_map.get(parent, {}).get(item_code, []):
item_tax_amount = flt((tax_amount * d.base_net_amount) / item_net_amount) \
if item_net_amount else 0
if item_tax_amount:
tax_amount = flt(item_tax_amount,
tax_amount_precision)
tax_amount = (tax_amount * -1 if
(doctype == 'Purchase Invoice'
and name in deducted_tax) else
tax_amount)
itemised_tax.setdefault(
d.name, {})[description] = frappe._dict({
"tax_rate":
tax_rate,
"tax_amount":
tax_amount
})
except ValueError:
continue
elif charge_type == "Actual" and tax_amount:
for d in invoice_item_row.get(parent, []):
itemised_tax.setdefault(
d.name, {})[description] = frappe._dict({
"tax_rate":
"NA",
"tax_amount":
flt((tax_amount * d.base_net_amount) /
d.base_net_total, tax_amount_precision)
})
tax_columns.sort()
for desc in tax_columns:
columns.append(desc + " Rate:Data:80")
columns.append(desc + " Amount:Currency/currency:100")
columns += [
"Total Tax:Currency/currency:80", "Total:Currency/currency:100"
]
return itemised_tax, tax_columns
def test_unescape(self):
from frappe.utils.xlsxutils import handle_html
val = handle_html("<p>html data ></p>")
self.assertIn("html data >", val)
self.assertEqual("abc", handle_html("abc"))
def export_query():
"""export from report builder"""
title = frappe.form_dict.title
frappe.form_dict.pop("title", None)
form_params = get_form_params()
form_params["limit_page_length"] = None
form_params["as_list"] = True
doctype = form_params.doctype
add_totals_row = None
file_format_type = form_params["file_format_type"]
title = title or doctype
del form_params["doctype"]
del form_params["file_format_type"]
if "add_totals_row" in form_params and form_params["add_totals_row"] == "1":
add_totals_row = 1
del form_params["add_totals_row"]
frappe.permissions.can_export(doctype, raise_exception=True)
if "selected_items" in form_params:
si = json.loads(frappe.form_dict.get("selected_items"))
form_params["filters"] = {"name": ("in", si)}
del form_params["selected_items"]
make_access_log(
doctype=doctype,
file_type=file_format_type,
report_name=form_params.report_name,
filters=form_params.filters,
)
db_query = DatabaseQuery(doctype)
ret = db_query.execute(**form_params)
if add_totals_row:
ret = append_totals_row(ret)
data = [["Sr"] + get_labels(db_query.fields, doctype)]
for i, row in enumerate(ret):
data.append([i + 1] + list(row))
data = handle_duration_fieldtype_values(doctype, data, db_query.fields)
if file_format_type == "CSV":
# convert to csv
import csv
from frappe.utils.xlsxutils import handle_html
f = StringIO()
writer = csv.writer(f)
for r in data:
# encode only unicode type strings and not int, floats etc.
writer.writerow([
handle_html(frappe.as_unicode(v)) if isinstance(
v, string_types) else v for v in r
])
f.seek(0)
frappe.response["result"] = cstr(f.read())
frappe.response["type"] = "csv"
frappe.response["doctype"] = title
elif file_format_type == "Excel":
from frappe.utils.xlsxutils import make_xlsx
xlsx_file = make_xlsx(data, doctype)
frappe.response["filename"] = title + ".xlsx"
frappe.response["filecontent"] = xlsx_file.getvalue()
frappe.response["type"] = "binary"
def get_tax_accounts(item_list, columns, company_currency,
doctype="Sales Invoice", tax_doctype="Sales Taxes and Charges"):
import json
item_row_map = {}
tax_columns = []
invoice_item_row = {}
itemised_tax = {}
tax_amount_precision = get_field_precision(frappe.get_meta(tax_doctype).get_field("tax_amount"),
currency=company_currency) or 2
for d in item_list:
invoice_item_row.setdefault(d.parent, []).append(d)
item_row_map.setdefault(d.parent, {}).setdefault(d.item_code or d.item_name, []).append(d)
conditions = ""
if doctype == "Purchase Invoice":
conditions = " and category in ('Total', 'Valuation and Total') and base_tax_amount_after_discount_amount != 0"
deducted_tax = get_deducted_taxes()
tax_details = frappe.db.sql("""
select
name, parent, description, item_wise_tax_detail,
charge_type, base_tax_amount_after_discount_amount
from `tab%s`
where
parenttype = %s and docstatus = 1
and (description is not null and description != '')
and parent in (%s)
%s
order by description
""" % (tax_doctype, '%s', ', '.join(['%s']*len(invoice_item_row)), conditions),
tuple([doctype] + invoice_item_row.keys()))
for name, parent, description, item_wise_tax_detail, charge_type, tax_amount in tax_details:
description = handle_html(description)
if description not in tax_columns and tax_amount:
# as description is text editor earlier and markup can break the column convention in reports
tax_columns.append(description)
if item_wise_tax_detail:
try:
item_wise_tax_detail = json.loads(item_wise_tax_detail)
for item_code, tax_data in item_wise_tax_detail.items():
itemised_tax.setdefault(item_code, frappe._dict())
if isinstance(tax_data, list):
tax_rate, tax_amount = tax_data
else:
tax_rate = tax_data
tax_amount = 0
if charge_type == "Actual" and not tax_rate:
tax_rate = "NA"
item_net_amount = sum([flt(d.base_net_amount)
for d in item_row_map.get(parent, {}).get(item_code, [])])
for d in item_row_map.get(parent, {}).get(item_code, []):
item_tax_amount = flt((tax_amount * d.base_net_amount) / item_net_amount) \
if item_net_amount else 0
if item_tax_amount:
tax_amount = flt(item_tax_amount, tax_amount_precision)
tax_amount = (tax_amount * -1
if (doctype == 'Purchase Invoice' and name in deducted_tax) else tax_amount)
itemised_tax.setdefault(d.name, {})[description] = frappe._dict({
"tax_rate": tax_rate,
"tax_amount": tax_amount
})
except ValueError:
continue
elif charge_type == "Actual" and tax_amount:
for d in invoice_item_row.get(parent, []):
itemised_tax.setdefault(d.name, {})[description] = frappe._dict({
"tax_rate": "NA",
"tax_amount": flt((tax_amount * d.base_net_amount) / d.base_net_total,
tax_amount_precision)
})
tax_columns.sort()
for desc in tax_columns:
columns.append(desc + " Rate:Data:80")
columns.append(desc + " Amount:Currency/currency:100")
columns += ["Total Tax:Currency/currency:80", "Total:Currency/currency:100"]
return itemised_tax, tax_columns
def get_tax_accounts(item_list, columns, company_currency,
doctype="Sales Invoice", tax_doctype="Sales Taxes and Charges"):
import json
item_row_map = {}
tax_columns = []
invoice_item_row = {}
itemised_tax = {}
conditions = ""
tax_amount_precision = get_field_precision(frappe.get_meta(tax_doctype).get_field("tax_amount"),
currency=company_currency) or 2
for d in item_list:
invoice_item_row.setdefault(d.parent, []).append(d)
item_row_map.setdefault(d.parent, {}).setdefault(d.item_code or d.item_name, []).append(d)
tax_details = frappe.db.sql("""
select
parent, description, item_wise_tax_detail,
base_tax_amount_after_discount_amount
from `tab%s`
where
parenttype = %s and docstatus = 1
and (description is not null and description != '')
and parent in (%s)
%s
order by description
""" % (tax_doctype, '%s', ', '.join(['%s']*len(invoice_item_row)), conditions),
tuple([doctype] + list(invoice_item_row)))
for parent, description, item_wise_tax_detail, tax_amount in tax_details:
description = handle_html(description)
if description not in tax_columns and tax_amount:
# as description is text editor earlier and markup can break the column convention in reports
tax_columns.append(description)
if item_wise_tax_detail:
try:
item_wise_tax_detail = json.loads(item_wise_tax_detail)
for item_code, tax_data in item_wise_tax_detail.items():
if not frappe.db.get_value("Item", item_code, "gst_hsn_code"):
continue
itemised_tax.setdefault(item_code, frappe._dict())
if isinstance(tax_data, list):
tax_amount = tax_data[1]
else:
tax_amount = 0
for d in item_row_map.get(parent, {}).get(item_code, []):
item_tax_amount = tax_amount
if item_tax_amount:
itemised_tax.setdefault(d.name, {})[description] = frappe._dict({
"tax_amount": flt(item_tax_amount, tax_amount_precision)
})
except ValueError:
continue
tax_columns.sort()
for desc in tax_columns:
columns.append(desc + " Amount:Currency/currency:160")
# columns += ["Total Amount:Currency/currency:110"]
return itemised_tax, tax_columns
def make_xlsx(data, report_name):
wb = openpyxl.Workbook()
ws = wb.create_sheet('All', 0)
wb.guess_types = True
customers = []
ws.append([''])
ws.append([''])
ws.append([''])
ws.append([''])
ws.append([''])
ws.append([''])
ws.append([''])
for row in data:
clean_row = []
if row[1] and row[1] not in customers and str(
row[1]).upper() not in ['CUSTOMER', 'CLIENT']:
customers.append(row[1])
for item in row:
if isinstance(
item,
string_types) and report_name != "Data Import Template":
value = item
else:
value = item
clean_row.append(value)
ws.append(clean_row)
ws = style_export(ws, report_name)
del data[0][1] #Remove the customer heading
#Create a sheet for each customer
if report_name not in ['Workshop Report', 'Workshop Daily Report']:
for customer in customers:
clean_title = customer.replace('\\', '')
clean_title = clean_title.replace('/', '')
wsc = wb.create_sheet(clean_title)
wsc.append([''])
wsc.append([''])
wsc.append([''])
wsc.append([''])
wsc.append([''])
wsc.append([''])
wsc.append([''])
wsc.append(data[0])
for row in data:
row = list(row)
if isinstance(row[1], string_types) and handle_html(
row[1]) == customer:
clean_row = []
del row[1]
for item in row:
if isinstance(
item, string_types
) and report_name != "Data Import Template":
value = item
else:
value = item
clean_row.append(value)
wsc.append(clean_row)
wsc = style_export(wsc, report_name)
xlsx_file = StringIO()
wb.remove_sheet(wb.get_sheet_by_name('Sheet'))
wb.save(xlsx_file)
return xlsx_file
