Odoo Help

Welcome!

This community is for beginners and experts willing to share their Odoo knowledge. It's not a forum to discuss ideas, but a knowledge base of questions and their answers.

0

How to get report data based on company_id

By
youta
on 5/2/16, 9:37 AM 350 views

Hello,

I'm trying to get Employees' payroll data for each company in a report.

Here's my code:

in my wizard:

class hr_wps_report(osv.osv_memory):

_name = 'hr.wps.report'

_description = 'WPS Report'

_columns = {

'date_from':fields.date("Start Date"),

'date_to':fields.date("End Date"),

'company_id': fields.many2one('res.company', 'Company', select=True, required=False),

}

_defaults = {

'date_from': lambda *a: time.strftime('%Y-%m-01'),

'date_to': lambda *a: str(datetime.now() + relativedelta.relativedelta(months=+1, day=1, days=-1))[:10],

'company_id': lambda self, cr, uid, context: self.pool.get('res.users').browse(cr, uid, uid, context=context).company_id.id,

}

def print_report(self, cr, uid, ids, data, context=None):

if context is None:

context = {}

data['form'] = self.read(cr, uid, ids, ['date_from', 'date_to', 'company_id'], context=context)[0]

return self.pool['report'].get_action(cr, uid, [], 'hr_wps.report_wpsreport', data=data, context=context)


in my report:

class wpsreport(report_sxw.rml_parse):

def __init__(self, cr, uid, name, context):

super(wpsreport, self).__init__(cr, uid, name, context=context)

self.localcontext.update({

'time': time,

'get_lines': self._get_lines,

})

def set_context(self, objects, data, ids, report_type=None):

self.date_start = data['form'].get('date_from', time.strftime('%Y-%m-%d'))

self.date_end = data['form'].get('date_to', time.strftime('%Y-%m-%d'))

self.company_id = data['form'].get('company_id')

return super(wpsreport, self).set_context(objects, data, ids, report_type=report_type)

def _get_lines(self, form):

res = []

self.cr.execute("""SELECT hr_employee.name_related,

(SELECT hr_payslip_line.total

FROM hr_payslip_line, hr_payslip

WHERE hr_employee.id = hr_payslip.employee_id

AND hr_payslip_line.slip_id = hr_payslip.id

AND hr_payslip_line.code = 'BASIC'

AND hr_payslip.date_from BETWEEN %s AND %s) as basic,

(SELECT hr_payslip_line.total

FROM hr_payslip_line, hr_payslip

WHERE hr_employee.id = hr_payslip.employee_id

AND hr_payslip_line.slip_id = hr_payslip.id

AND hr_payslip_line.code = 'ALLOWANCES'

AND hr_payslip.date_from BETWEEN %s AND %s) as total_allowance,

(SELECT hr_payslip_line.total

FROM hr_payslip_line, hr_payslip

WHERE hr_employee.id = hr_payslip.employee_id

AND hr_payslip_line.slip_id = hr_payslip.id

AND hr_payslip_line.code = 'DEDUCTIONS'

AND hr_payslip.date_from BETWEEN %s AND %s) as total_deduction,

(SELECT hr_payslip_line.total

FROM hr_payslip_line, hr_payslip

WHERE hr_employee.id = hr_payslip.employee_id

AND hr_payslip_line.slip_id = hr_payslip.id

AND hr_payslip_line.code = 'NET'

AND hr_payslip.date_from BETWEEN %s AND %s) as total

FROM hr_employee,

hr_payslip,

res_company

WHERE hr_payslip.employee_id = hr_employee.id

AND hr_payslip.company_id = res_company.id

AND hr_payslip.date_from BETWEEN %s AND %s

AND hr_payslip.company_id = %s""", (self.date_start, self.date_end, self.date_start, self.date_end, self.date_start, self.date_end, self.date_start, self.date_end, self.date_start, self.date_end, self.company_id))

 

employees = self.cr.dictfetchall()

for emp in employees:

value = {}

value['name_related'] = emp['name_related']

value['basic'] = emp['basic']

value['total_allowance'] = emp['total_allowance']

value['total_deduction'] = emp['total_deduction']

value['total'] = emp['total']

value['No_working_days'] = 30

value['extra_hours'] = 0

res.append(value)

return res


But I'm getting Error of:

QWebException: "invalid input syntax for integer: "Yourcompany" 

AND hr_payslip.company_id = ARRAY[9, 'Yourcompany']

How to fix it?

1

Ahmed M.Elmubarak

--Ahmed M.Elmubarak--
2988
| 5 3 5
Sudan
--Ahmed M.Elmubarak--


Ahmed M.Elmubarak
On 5/3/16, 3:36 AM

Hello,

Try these:

self.company_id.id

or 

self.company_id[0]

instead of self.company_id in the query ..


Hope it'll work

This one worked self.company_id[0] Thank you very much.. you're my hero Ahmed :D However, can you explain it.. I mean the error, in other words what is the difference between self.company_id[0] and self.company_id

youta
on 5/3/16, 6:34 AM

Thanx so much for cheering :] . When you use self.company_id it will return a list containing the [ID, name of the record] as your case here [9, 'Yourcompany'], which your query don't expect that value, so you just need to fetch the ID from the list, when you use the self.company_id[0] then you're fetching the first element in the list which is the Id ...

Ahmed M.Elmubarak
on 5/3/16, 8:58 AM

Your Answer

Please try to give a substantial answer. If you wanted to comment on the question or answer, just use the commenting tool. Please remember that you can always revise your answers - no need to answer the same question twice. Also, please don't forget to vote - it really helps to select the best questions and answers!

About This Community

This community is for professionals and enthusiasts of our products and services. Read Guidelines

Question tools

1 follower(s)

Stats

Asked: 5/2/16, 9:37 AM
Seen: 350 times
Last updated: 5/3/16, 6:32 AM