After a lot of research, trial and error, I got the script reports working. .
-
- Create a custom module (see the creating a custom app/fixtures process)
-
- Create a report [New Report]. Set the Module as the custom module. Set ‘Standard’=Yes.
-
- Go over to the custom module folder /frappe-bench/apps/cspl/cspl/cspl/report/monthly_fab_report - you might need to create the last folder
-
- copy over the .js, .json, .py and .html files from a simple report. I used client_statement from another forum member
erpnext/erpnext/accounts/report/client_statement at client_statement · pradeshc/erpnext · GitHub
- copy over the .js, .json, .py and .html files from a simple report. I used client_statement from another forum member
-
- After this it is a question of tinkering with the various functions to get the result you need.
One thing I learnt is that I prefer to pass F-strings to frappe.db.sql rather than using the method used in client_statement.py
so a typical function would look like:
def get_data(filters):
filter_doc_status = """ (0, 1) """ if filters.show_drafts == 1 else """ (1)""" ;
print(filter_doc_status);
filter_cust = """ and tw1.customer = '""" + filters.customer + "'" if filters.customer else ""
s_sql = f"""
select
twm.project , date_format(twm.measurement_date__time, '%%Y-%%m') as month ,
format(sum(twm.calculated_daily_volume),4) calculated_daily_volume,
sum(twm.num_readings) as num_readings
from (
select
docstatus, project
, date_format(measurement_date__time,"%%Y-%%m-%%d") as measurement_date__time
, format(avg(calculated_daily_volume),4) as calculated_daily_volume
, count(calculated_daily_volume) as num_readings
from `tabWater Height Measurement` tw1
where
tw1.docstatus in {filter_doc_status}
{filter_cust}
and tw1.measurement_date__time between \'{filters['from_date']}\' and \'{filters['to_date']}\'
group by project, date_format(measurement_date__time,"%%Y-%%m-%%d")
order by project, measurement_date__time
)
as twm
group by twm.project, month
order by twm.project, month """;
print(s_sql);
return frappe.db.sql(s_sql,filters, as_dict=1)