eiru_reports_dashboard/controllers/helpers/hr_employee.py

# -*- coding: utf-8 -*-
from openerp.http import request as r

def get_hr_payslip_widget():
    user_store = r.env.user.store_id.id

    validate = '''
        SELECT EXISTS(
            SELECT table_name
            FROM information_schema.columns
            WHERE table_schema='public'
                AND table_name='hr_employee'
                AND column_name='store_id')
    '''

    query = '''
        SELECT
        	payslip.id,
        	payslip.number,
        	payslip.create_date,
        	employee.name_related,
        	payslip_line.code,
        	CASE
        		WHEN payslip_line.code = 'NET'
        		THEN payslip_line.total
        		END AS NETO,
        	CASE
        		WHEN payslip_line.code = 'IPSC'
        		THEN payslip_line.total
        		END AS IPSC,
        	CASE
        		WHEN payslip_line.code = 'IPSE'
        		THEN payslip_line.total
        		END AS IPSE
        FROM hr_payslip AS payslip
        LEFT JOIN hr_employee AS employee
        ON employee.id = payslip.employee_id
        LEFT JOIN hr_payslip_line AS payslip_line
        ON payslip_line.slip_id = payslip.id
        WHERE TO_CHAR(payslip.date_to,'YYYY-MM') = TO_CHAR(current_date,'YYYY-MM')
        AND payslip.state in ('done','paid')
        AND payslip_line.code in ('NET','IPSC','IPSE')
        AND employee.store_id = ''' + str(user_store) + '''
        GROUP BY
        	payslip.id,
        	payslip.number,
        	employee.name_related,
        	payslip_line.code,
        	payslip_line.total
    '''

    r.cr.execute(validate)
    for j in r.cr.fetchall():
        band = j[0]

    if band == True:
        r.cr.execute(query)
        return [
            {
                'payslip_id': j[0],
                'number': j[1],
                'date': j[2],
                'name': j[3],
                'code': j[4],
                'net': j[5],
                'ipsc': j[6],
                'ipse': j[7],
            } for j in r.cr.fetchall()
        ]