I have a script report called Trip Report MIS. For this report I have multiple filters. One of them is Date Range. On this filter I want to let user select a range of data and based on that it should filter docs that were created within that range (I have a field ‘created_on’). How do i make this happen.
Here is my code
.js
frappe.query_reports["Trip Report MIS V2"] = {
"filters": [
{
'fieldname': 'name',
'label': __('Trip Id'),
'fieldtype': 'Link',
'options': 'Trips'
},
{
'fieldname': 'lr_number',
'label': __('LR Number'),
'fieldtype': 'Data',
},
{
'fieldname': 'vendor',
'label': __('Vendor'),
'fieldtype': 'Link',
'options': 'Vendor'
},
{
'fieldname': 'vehicle_number',
'label': __('Vehicle Number'),
'fieldtype': 'Link',
'options': 'Vehicle'
},
{
'fieldname': 'origin',
'label': __('Source/Origin'),
'fieldtype': 'Link',
'options': 'Geofence'
},
{
'fieldname': 'destination',
'label': __('Destination'),
'fieldtype': 'Link',
'options': 'Geofence'
},
{
'fieldname': 'driver_number',
'label': __('Driver Number'),
'fieldtype': 'Data'
},
{
'fieldname': 'created_at',
'label': 'Date Range',
'fieldtype': 'Date'
},
{
'fieldname': 'status',
'label': __('Status'),
'fieldtype': 'Select',
'options': ['', 'Driver Consent Pending', 'Loading', 'Enroute to Destination', 'Unloading', 'Completed', 'Stopped', 'Completed & POD received'],
'default': ''
},
]
};
.py
def execute(filters=None):
print("Inside execute()", "%" * 30)
if not filters:
filters = {}
columns = get_columns()
data = get_data(filters)
return columns, data
def get_columns():
print("Inside get_columns()", "%" * 30)
return [
{
'fieldname': 'customer',
'label': 'Customer',
'fieldtype': 'Data',
},
{
'fieldname': 'trip_id',
'label': 'Trip Id',
'fieldtype': 'Data',
},
{
'fieldname': 'lr_number',
'label': 'LR Number',
'fieldtype': 'Data',
},
{
'fieldname': 'user',
'label': 'User',
'fieldtype': 'Data',
},
{
'fieldname': 'source',
'label': 'Source',
'fieldtype': 'Data',
},
{
'fieldname': 'destination',
'label': 'Destination',
'fieldtype': 'Data',
},
{
'fieldname': 'driver_name',
'label': 'Driver Name',
'fieldtype': 'Data',
},
{
'fieldname': 'driver_mobile',
'label': 'Driver Mobile',
'fieldtype': 'Data',
},
{
'fieldname': 'vehicle',
'label': 'Vehicle',
'fieldtype': 'Data',
},
{
'fieldname': 'status',
'label': 'Status',
'fieldtype': 'Data',
},
{
'fieldname': 'start_time',
'label': 'Start Time',
'fieldtype': 'Data',
},
{
'fieldname': 'completed_at',
'label': 'Completed At',
'fieldtype': 'Data',
},
{
'fieldname': 'total_distance',
'label': 'Total Distance',
'fieldtype': 'Data',
},
{
'fieldname': 'total_distance_travel',
'label': 'Total Distance Travel',
'fieldtype': 'Data',
},
{
'fieldname': 'pending_km',
'label': 'Pending Km',
'fieldtype': 'Data',
},
{
'fieldname': 'eta',
'label': 'ETA',
'fieldtype': 'Data',
},
{
'fieldname': 'daywise_dist_travel',
'label': 'Daywise Dist. Travel',
'fieldtype': 'Data',
},
{
'fieldname': 'origin_in',
'label': 'Origin In',
'fieldtype': 'Data',
},
{
'fieldname': 'origin_out',
'label': 'Origin Out',
'fieldtype': 'Data',
},
{
'fieldname': 'loading_time',
'label': 'Loading Time (Days, Hh:Mm:sec)',
'fieldtype': 'Data',
},
{
'fieldname': 'destination_in',
'label': 'Destination In',
'fieldtype': 'Data',
},
{
'fieldname': 'destination_out',
'label': 'Destination Out',
'fieldtype': 'Data',
},
{
'fieldname': 'unloading_time',
'label': 'Unloading Time (Days, Hh:Mm:sec)',
'fieldtype': 'Data',
},
{
'fieldname': 'total_halt_time',
'label': 'Total Halt Time',
'fieldtype': 'Duration',
},
{
'fieldname': 'current_address',
'label': 'Current Address',
'fieldtype': 'Data',
},
{
'fieldname': 'last_tracked_at',
'label': 'Last Tracked At',
'fieldtype': 'Data',
},
{
'fieldname': 'created_at',
'label': 'Created On (Date Hh:Mm:sec)',
'fieldtype': 'Datetime',
},
{
'fieldname': 'track',
'label': 'Track',
'fieldtype': 'HTML',
},
]
def get_data(filters):
print("Inside get_data()", "%" * 30)
conditions = get_conditions(filters)
trip_list = frappe.get_all(
doctype='Trips',
fields=['owner','name','customer','trip_uid','lr_number','origin','destination','driver_name','driver_number','vehicle_number',
'status','created_at','last_update','total_distance','distance_travel','eta','origin_in',
'origin_out','destination_in','destination_out','last_location','last_updated_at','total_halt_time', 'created_at'],
filters=conditions
)
d = []
for trip in trip_list:
trip_doc = frappe.get_doc('Trips', trip['name'])
daywise_str = ""
for i, e in enumerate(trip_doc.daywise_travel):
daywise_str = daywise_str + "{} -- {} Km || ".format(e.date, e.distance_travel)
loading_time = 0
unloading_time = 0
if (trip['origin_in'] != None and trip['origin_in'] != "") and (trip['origin_out'] != None and trip['origin_out'] != ""):
loading_time = trip['origin_out'] - trip['origin_in']
if (trip['destination_in'] != None and trip['destination_in'] != "") and (trip['destination_out'] != None and trip['destination_out'] != ""):
unloading_time = trip['destination_out'] - trip['destination_in']
track_link = "www.test.com";
info = {
'customer': trip['customer'],
'trip_id': trip['name'],
'lr_number': trip['lr_number'],
'user': trip['owner'],
'source': trip['origin'],
'destination': trip['destination'],
'driver_name': trip['driver_name'],
'driver_mobile': trip['driver_number'],
'vehicle': trip['vehicle_number'],
'status': trip['status'],
'start_time': trip['created_at'],
'completed_at': trip['last_update'],
'total_distance': '{} Km'.format(trip['total_distance']),
'total_distance_travel': '{} Km'.format(trip['distance_travel']),
'pending_km': '{} Km'.format(trip['total_distance'] - trip['distance_travel']),
'eta': trip['eta'],
'daywise_dist_travel': daywise_str,
'origin_in': trip['origin_in'],
'origin_out': trip['origin_out'],
'loading_time': '{}'.format(loading_time),
'destination_in': trip['destination_in'],
'destination_out': trip['destination_out'],
'unloading_time': '{}'.format(unloading_time),
'total_halt_time': trip['total_halt_time'],
'current_address': trip['last_location'],
'last_tracked_at': trip['last_updated_at'],
'created_at': trip['created_at'],
'track': '<a style="color: blue;" target="_blank" href="{0}">{1}</a>'.format(track_link,track_link)
}
d.append(info)
return d
def get_conditions(filters):
print("Inside get_conditions()", "%" * 30)
conditions = {}
for key, value in filters.items():
if filters.get(key):
conditions[key] = value
return conditions