Dear All,
Am trying to create a qweb report to basically print out a table of all Vehicle service logs using a custom sql view, but my report always shows blank besides the table headers. Am using odoo 10 CE. not sure what am doing wrong and or help will be greatly appreciated
my fleet.rpt.py
from odoo import fields, models, tools, api
class FleetRptView(models.Model):
_name = 'fleet.vehicle.report.view'
_description = 'Custom fleet Report'
_auto = False
id = fields.Integer('Service')
date = fields.Date('Date')
vehicle = fields.Char('Vehicle Name')
maintype = fields.Char('Service Description')
servicetype = fields.Char('ServiceType')
total = fields.Float('TotalAmt')
unitprice = fields.Float('UnitPrice')
odometer = fields.Integer('Odometer')
service_id = fields.Integer('ServiceId')
parent_id = fields.Integer('ParentId')
actotal = fields.Integer('ActualTotal')
vehicle_id = fields.Integer('VehicleId')
purchaser_id = fields.Many2one('res.partner','Purchaser')
vendor_id = fields.Many2one('res.partner','Vendor')
@api.model_cr
def init(self):
tools.drop_view_if_exists(self.env.cr, self._table)
self.env.cr.execute("""CREATE or REPLACE VIEW %s as (
WITH cte_cost AS(
SELECT p.id
,p.parent_id
,t.name as maintype
,t2.name as type
,p.cost_type
,p.amount
,c.date
,c.name as vehicle
,p.vehicle_id
,c.odometer_id
FROM fleet_vehicle_cost c
JOIN fleet_vehicle_cost p ON c.id=p.parent_id --parent join
JOIN fleet_service_type t ON c.cost_subtype_id=t.id
JOIN fleet_service_type t2 ON p.cost_subtype_id=t2.id
)
SELECT
c.id as id
,c.date
,c.vehicle as vehicle
,c.maintype as maintype
,c.type as servicetype
,s.cost_amount as total
,c.amount as unitprice
,o.value as odometer
,s.id as service_id
,c.parent_id as parent_id
,SUM(c.amount) OVER (PARTITION BY c.parent_id) as actotal
,c.vehicle_id
,s.purchaser_id
,s.vendor_id
FROM fleet_vehicle_log_services s
JOIN cte_cost c ON s.cost_id=c.parent_id and cost_type='services'
LEFT JOIN fleet_vehicle_odometer o ON c.odometer_id=o.id
ORDER BY c.date desc, c.id,c.parent_id
)""" % (self._table))
report xml: fleet_report.xml
<?xml version="1.0" encoding="utf-8"?>
<odoo>
<report id="action_fleet_rpt_report"
string="Custom Fleet Report"
model="fleet.vehicle.report.view"
report_type="qweb-html"
name="fleet_rpt.rpt_template"
/>
<template id="rpt_template">
<t t-call="report.html_container">
<t t-call="report.external_layout">
<div class="page">
<table class="table table-striped">
<tr>
<th>Date </th>
<th>Vehicle </th>
<th>Type of Service </th>
<th>km Covered </th>
<th>Problem/ Service details</th>
<th>Unit Price</th>
<th>Total</th>
</tr>
<t t-foreach="docs" t-as="o">
<tr>
<td>
<span t-field="o.date" />
</td>
<td>
<span t-field="o.vehicle"/>
</td>
<td>
<span t-field="o.maintype"/>
</td>
<td>
<span t-field="o.odometer"/>
</td>
<td>
<span t-field="o.servicetype"/>
</td>
<td>
<span t-field="o.unitprice"/>
</td>
</tr>
</t>
</table>
</div>
</t>
</t>
</template>
</odoo>