In this report we have three column (Domain, Knowledge Area and Recommendation). The purpose of group by in reports is to show "Engineering" and "Non Engineering" only once in each row of "Domain" column. For example our "Knowledge Area" column has only one item in "Engineering" domain and three items in "Non Engineering" domain. Here we face a problem that if we did not group by our domain it repeats as the total number of our "Engineering Knowledge"
So its very simple to group by data in any qweb / custom reports in Odoo/OpenErp.
Xml Code Snipet:
<div>
<table class="table report_border table-hover table-striped">
<thead>
<tr>
<th style="padding: 0px;" class="text-center"><h5><b>Domain</b></h5></th>
<th style="padding: 0px;" class="text-center"><h5><b>Knowledge Area</b></h5></th>
<th style="padding: 0px;" class="text-center"><h5><b>PEC/HEC Recommended</b</h5></th>
</tr>
<tr>
<th></th>
<th></th>
<th>
<table class="table report_border" style="margin-bottom:0px;">
<tr>
<td style="width:150px; padding: 0px 5px;" class="text-center"><h5>Total Credits</h5></td>
<td style="width:150px; padding: 0px 5px;" class="text-center"><h5>Overall %</h5></td>
</tr>
</table>
</th>
</tr>
</thead>
<tbody>
<tr>
<t t-set="total" t-value="0.0"/>
<t t-foreach="main_list1" t-as="dom">
<t t-foreach="dom" t-as="do">
<t t-foreach="do" t-as="d">
<t t-set="total" t-value="total + d.subject_id.credit_hours"/>
</t>
</t>
</t>
<t t-foreach="main_list1" t-as="d">
<tr>
<td class="text-center" style="vertical-align:middle;">
<span t-esc="d[0][0].subject_id.domain.name"/>
</td>
<td>
<table class="table none_border" style="margin-bottom:0px;">
<t t-foreach="d" t-as="ka">
<tr>
<td style="padding: 0px 5px;">
<h5><span t-esc="ka[0].subject_id.knowledgearea.name"/></h5>
</td>
</tr>
</t>
<tr>
<td style="padding: 0px 5px;" class="text-right"><h5><b>Sub-Total</b></h5></td>
</tr>
</table>
</td>
<td>
<table class="table none_border" style="margin-bottom:0px;">
<t t-set="sub_total" t-value="0"/>
<t t-foreach="d" t-as="ka">
<tr>
<td style="padding: 0px 5px;">
<t t-set="total_credithours" t-value="0"/>
<t t-foreach="ka" t-as="cred">
<t t-set="total_credithours" t-value="total_credithours+cred.subject_id.credit_hours" />
</t>
<h5><span t-esc="total_credithours"/></h5>
</td>
</tr>
<t t-foreach="ka" t-as="sub_t">
<t t-set="sub_total" t-value="sub_total + sub_t.subject_id.credit_hours" />
</t>
</t>
<tr>
<td class="text-left" style="width:150px; padding: 0px 5px;"><h5><b><t t-esc="sub_total" /></b></h5></td>
<td class="text-center" style="width:150px; padding: 0px 5px;"><h5><t t-esc="'%.2f'%((sub_total / total)*100)" />%</h5></td>
</tr>
</table>
</td> </tr>
</t>
<tr>
<td></td>
<td class="text-right"><h5><b>Total</b></h5></td>
<td class="text-left"><h5><b><t t-esc="total" /></b></h5></td>
</tr>
</tr>
</tbody>
</table>
</div>
Python Code:
# custom report for curriculum design
class curriculum_design_rep(models.AbstractModel):
_name = 'report.obe_reports_hec.curriculum_design_report'
@api.multi
def render_html(self,data=None):
report_obj = self.env['report']
report = report_obj._get_report_from_name('obe_reports_hec.curriculum_design_report')
# doc_id = self._ids[0]
if self._ids:
print('#######################*****', self._ids)
form_session_id = self._ids[0]
session_id = self.env['obe.core.session'].sudo().search([('id', '=', form_session_id)])
else:
print('$$$$$$$$$$$$Data from Button$$$$$$$$$$$$$$$',data['session_id'])
session_id = self.env['obe.core.session'].search([('id', '=', data['session_id'])])
program_catalogue_id = session_id.program_catalogue_id.id
myReport = self.env['obe.core.program.catalogue'].search([('id', '=', program_catalogue_id)])#'id','in',self._ids
print('ttttttttttttttttt', myReport.department_id.name)
session_name = myReport[0].name
# print(myReport[0].id)
subjectss = self.env['obe.core.semester.subject'].search([('program_catalogue_id','=',myReport[0].id)])
campus_name = session_id.department_id.campus_id.name
## dd/mm/yyyy format
cur_date = time.strftime("%d-%b-%Y")
print (cur_date)
subjectss = subjectss.sorted(key=lambda r: r.subject_id.domain.id)
print('ppppppppppppppp',subjectss)
newgroup = []
domain_list = []
b = []
for k, v in groupby(subjectss, lambda x: x.subject_id.domain):
v=sorted(v,key=lambda sb:sb.subject_id.knowledgearea.name)
# print('vvvvvvvvvvvvvv', v)
domain_list.append(v)
a = []
for kw_key,kw_value in groupby(v,lambda ka:ka.subject_id.knowledgearea):
a.append(list(kw_value))
for sem_sub in a:
cr_list = []
for sub in sem_sub:
cr_list.append(sub.subject_id.credit_hours)
print('qqqqqqqqq',sum(cr_list))
b.append(a)
print('bbbbbbb', b)
#print(self.env['obe.core.subject'].search([]).ids)
docargs = {
'doc_ids': self.env['obe.core.program.catalogue'].search([]).ids,
'doc_model': 'report.obe.core.program.catalogue',
'docs': newgroup,
'session_namee': session_name,
'curr_date': cur_date,
'campus_name': campus_name,
'dep_name':myReport[0].department_id.name,
'main_list1': b,
}
return report_obj.render('obe_reports_hec.curriculum_design_report', docargs)
<table class="table report_border table-hover table-striped">
<thead>
<tr>
<th style="padding: 0px;" class="text-center"><h5><b>Domain</b></h5></th>
<th style="padding: 0px;" class="text-center"><h5><b>Knowledge Area</b></h5></th>
<th style="padding: 0px;" class="text-center"><h5><b>PEC/HEC Recommended</b</h5></th>
</tr>
<tr>
<th></th>
<th></th>
<th>
<table class="table report_border" style="margin-bottom:0px;">
<tr>
<td style="width:150px; padding: 0px 5px;" class="text-center"><h5>Total Credits</h5></td>
<td style="width:150px; padding: 0px 5px;" class="text-center"><h5>Overall %</h5></td>
</tr>
</table>
</th>
</tr>
</thead>
<tbody>
<tr>
<t t-set="total" t-value="0.0"/>
<t t-foreach="main_list1" t-as="dom">
<t t-foreach="dom" t-as="do">
<t t-foreach="do" t-as="d">
<t t-set="total" t-value="total + d.subject_id.credit_hours"/>
</t>
</t>
</t>
<t t-foreach="main_list1" t-as="d">
<tr>
<td class="text-center" style="vertical-align:middle;">
<span t-esc="d[0][0].subject_id.domain.name"/>
</td>
<td>
<table class="table none_border" style="margin-bottom:0px;">
<t t-foreach="d" t-as="ka">
<tr>
<td style="padding: 0px 5px;">
<h5><span t-esc="ka[0].subject_id.knowledgearea.name"/></h5>
</td>
</tr>
</t>
<tr>
<td style="padding: 0px 5px;" class="text-right"><h5><b>Sub-Total</b></h5></td>
</tr>
</table>
</td>
<td>
<table class="table none_border" style="margin-bottom:0px;">
<t t-set="sub_total" t-value="0"/>
<t t-foreach="d" t-as="ka">
<tr>
<td style="padding: 0px 5px;">
<t t-set="total_credithours" t-value="0"/>
<t t-foreach="ka" t-as="cred">
<t t-set="total_credithours" t-value="total_credithours+cred.subject_id.credit_hours" />
</t>
<h5><span t-esc="total_credithours"/></h5>
</td>
</tr>
<t t-foreach="ka" t-as="sub_t">
<t t-set="sub_total" t-value="sub_total + sub_t.subject_id.credit_hours" />
</t>
</t>
<tr>
<td class="text-left" style="width:150px; padding: 0px 5px;"><h5><b><t t-esc="sub_total" /></b></h5></td>
<td class="text-center" style="width:150px; padding: 0px 5px;"><h5><t t-esc="'%.2f'%((sub_total / total)*100)" />%</h5></td>
</tr>
</table>
</td> </tr>
</t>
<tr>
<td></td>
<td class="text-right"><h5><b>Total</b></h5></td>
<td class="text-left"><h5><b><t t-esc="total" /></b></h5></td>
</tr>
</tr>
</tbody>
</table>
</div>
Python Code:
# custom report for curriculum design
class curriculum_design_rep(models.AbstractModel):
_name = 'report.obe_reports_hec.curriculum_design_report'
@api.multi
def render_html(self,data=None):
report_obj = self.env['report']
report = report_obj._get_report_from_name('obe_reports_hec.curriculum_design_report')
# doc_id = self._ids[0]
if self._ids:
print('#######################*****', self._ids)
form_session_id = self._ids[0]
session_id = self.env['obe.core.session'].sudo().search([('id', '=', form_session_id)])
else:
print('$$$$$$$$$$$$Data from Button$$$$$$$$$$$$$$$',data['session_id'])
session_id = self.env['obe.core.session'].search([('id', '=', data['session_id'])])
program_catalogue_id = session_id.program_catalogue_id.id
myReport = self.env['obe.core.program.catalogue'].search([('id', '=', program_catalogue_id)])#'id','in',self._ids
print('ttttttttttttttttt', myReport.department_id.name)
session_name = myReport[0].name
# print(myReport[0].id)
subjectss = self.env['obe.core.semester.subject'].search([('program_catalogue_id','=',myReport[0].id)])
campus_name = session_id.department_id.campus_id.name
## dd/mm/yyyy format
cur_date = time.strftime("%d-%b-%Y")
print (cur_date)
subjectss = subjectss.sorted(key=lambda r: r.subject_id.domain.id)
print('ppppppppppppppp',subjectss)
newgroup = []
domain_list = []
b = []
for k, v in groupby(subjectss, lambda x: x.subject_id.domain):
v=sorted(v,key=lambda sb:sb.subject_id.knowledgearea.name)
# print('vvvvvvvvvvvvvv', v)
domain_list.append(v)
a = []
for kw_key,kw_value in groupby(v,lambda ka:ka.subject_id.knowledgearea):
a.append(list(kw_value))
for sem_sub in a:
cr_list = []
for sub in sem_sub:
cr_list.append(sub.subject_id.credit_hours)
print('qqqqqqqqq',sum(cr_list))
b.append(a)
print('bbbbbbb', b)
#print(self.env['obe.core.subject'].search([]).ids)
docargs = {
'doc_ids': self.env['obe.core.program.catalogue'].search([]).ids,
'doc_model': 'report.obe.core.program.catalogue',
'docs': newgroup,
'session_namee': session_name,
'curr_date': cur_date,
'campus_name': campus_name,
'dep_name':myReport[0].department_id.name,
'main_list1': b,
}
return report_obj.render('obe_reports_hec.curriculum_design_report', docargs)