Hi everyone! I have a custom module for reporting and scripting.
I’ve created a new Script report and copy the exact content of the “Accounts Receivable Report” incluiding the html file, js and py. When I run the report I get the following error:
Traceback (most recent call last):
File "/home/frappe/frappe-bench/apps/frappe/frappe/app.py", line 57, in application
response = frappe.handler.handle()
File "/home/frappe/frappe-bench/apps/frappe/frappe/handler.py", line 22, in handle
data = execute_cmd(cmd)
File "/home/frappe/frappe-bench/apps/frappe/frappe/handler.py", line 53, in execute_cmd
return frappe.call(method, **frappe.form_dict)
File "/home/frappe/frappe-bench/apps/frappe/frappe/__init__.py", line 935, in call
return fn(*args, **newargs)
File "/home/frappe/frappe-bench/apps/frappe/frappe/desk/query_report.py", line 96, in run
res = frappe.get_attr(method_name)(frappe._dict(filters))
File "/home/frappe/frappe-bench/apps/p4d/p4d/p4d/report/cobros/cobros.py", line 331, in execute
return ReceivablePayableReports(filters).run(args)
File "/home/frappe/frappe-bench/apps/p4d/p4d/p4d/report/cobros/cobros.py", line 20, in run
data = self.get_data(party_naming_by, args)
File "/home/frappe/frappe-bench/apps/p4d/p4d/p4d/report/cobros/cobros.py", line 94, in get_data
future_vouchers = self.get_entries_after(self.filters.report_date, args.get("party_type"))
File "/home/frappe/frappe-bench/apps/p4d/p4d/p4d/report/cobros/cobros.py", line 160, in get_entries_after
return list(set([(e.voucher_type, e.voucher_no) for e in self.get_gl_entries(party_type)
File "/home/frappe/frappe-bench/apps/p4d/p4d/p4d/report/cobros/cobros.py", line 260, in get_gl_entries
.format(select_fields, conditions), values, as_dict=True)
File "/home/frappe/frappe-bench/apps/frappe/frappe/database.py", line 152, in sql
self._cursor.execute(query, values)
File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 250, in execute
self.errorhandler(self, exc, value)
File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler
raise errorvalue
OperationalError: (1054, "Unknown column 'due_date' in 'field list'")
I checked 252 line which is:
self.gl_entries = frappe.db.sql("""select name, posting_date, account, party_type, party,
voucher_type, voucher_no, against_voucher_type, against_voucher, due_date,
account_currency, remarks, {0}
from `tabGL Entry`
where docstatus < 2 and party_type=%s and (party is not null and party != '') {1}
group by voucher_type, voucher_no, against_voucher_type, against_voucher, party, due_date
order by posting_date, party"""
.format(select_fields, conditions), values, as_dict=True)
It seems that due_date is not defined in the “tabGL Entry” table. When I check the database structure I don’t find it:
+----------------------------+---------------+------+-----+----------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+---------------+------+-----+----------+-------+
| name | varchar(140) | NO | PRI | NULL | |
| creation | datetime(6) | YES | | NULL | |
| modified | datetime(6) | YES | | NULL | |
| modified_by | varchar(140) | YES | | NULL | |
| owner | varchar(140) | YES | | NULL | |
| docstatus | int(1) | NO | | 0 | |
| parent | varchar(140) | YES | MUL | NULL | |
| parentfield | varchar(140) | YES | | NULL | |
| parenttype | varchar(140) | YES | | NULL | |
| idx | int(8) | NO | | 0 | |
| voucher_type | varchar(140) | YES | | NULL | |
| voucher_no | varchar(140) | YES | MUL | NULL | |
| cost_center | varchar(140) | YES | | NULL | |
| credit | decimal(18,6) | NO | | 0.000000 | |
| party_type | varchar(140) | YES | | NULL | |
| transaction_date | date | YES | | NULL | |
| debit | decimal(18,6) | NO | | 0.000000 | |
| party | varchar(140) | YES | MUL | NULL | |
| _liked_by | text | YES | | NULL | |
| company | varchar(140) | YES | | NULL | |
| _assign | text | YES | | NULL | |
| fiscal_year | varchar(140) | YES | | NULL | |
| _comments | text | YES | | NULL | |
| is_advance | varchar(140) | YES | | NULL | |
| remarks | text | YES | | NULL | |
| account_currency | varchar(140) | YES | | NULL | |
| debit_in_account_currency | decimal(18,6) | NO | | 0.000000 | |
| _user_tags | text | YES | | NULL | |
| account | varchar(140) | YES | MUL | NULL | |
| against_voucher_type | varchar(140) | YES | | NULL | |
| against | text | YES | | NULL | |
| project | varchar(140) | YES | | NULL | |
| against_voucher | varchar(140) | YES | MUL | NULL | |
| is_opening | varchar(140) | YES | | NULL | |
| posting_date | date | YES | MUL | NULL | |
| credit_in_account_currency | decimal(18,6) | NO | | 0.000000 | |
+----------------------------+---------------+------+-----+----------+-------+
The question is… why is this script working in the accouts receivable report but the same script is not in my custom app? Does anyone have a clue on this?