eiru_reports_sales/controller/helpers/res_partner.py

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

def get_customers():
    query = '''
        SELECT
            partner.id,
            partner.name,
            partner.ruc,
            partner.customer,
            partner.phone,
            partner.mobile,
            partner.email,
            partner.street,
            partner.street2,
            country.name,
            CASE
        		WHEN state.parent_id IS NOT NULL
        		THEN ( parent_state.name || ' / ' || state.name)
        		ELSE state.name
        		END AS state,
            partner.store_id,
            partner.company_id
        FROM res_partner AS partner
        LEFT JOIN res_country AS country
        ON country.id = partner.country_id
        LEFT JOIN res_country_state AS state
        ON state.id = partner.state_id
        LEFT JOIN res_country_state AS parent_state
        ON parent_state.id = state.parent_id
        WHERE partner.customer = true
    '''

    r.cr.execute(query)

    return [
        {
            'id': j[0],
            'name': j[1],
            'ruc': j[2],
            'customer': j[3],
            'phone': j[4],
            'mobile': j[5],
            'email': j[6],
            'street': j[7],
            'street2': j[8],
            'country': j[9],
            'state': j[10],
            'store_id': j[11],
            'company_id': j[12],
        } for j in r.cr.fetchall()
    ]