eiru_reports_servicio/controllers/helpers/sale_order.py

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

def get_sale_order_line():
    query = '''
        SELECT
            sale_line.id,
            sale.id,
            sale.name,
            sale.date_order,
            ARRAY[CAST(company.id AS VARCHAR),company.name] as company,
            ARRAY[CAST(partner.id AS VARCHAR), partner.name] AS partner,
            product.name_template,
            ARRAY[CAST(sale_line.medic_id AS VARCHAR), salesdoctor.name] AS salesdoctor,
            ARRAY[CAST(brand.id AS VARCHAR), brand.name] AS brand,
            ARRAY[CAST(category.id AS VARCHAR), category.name] AS category,
            sale_line.price_unit,
            sale_line.product_uos_qty,
            sale_line.discount,
            (array_agg(distinct tax.amount)) AS tax,
            (array_agg(distinct attr_rel.att_id)) AS attr_rel,
            (array_agg(distinct attr.id)) AS attr,
            (array_agg(distinct attr_value.name)) AS attr_value,
            ARRAY[CAST(sale_line.salesman_id AS VARCHAR), salesman.name] AS salesman,
            ARRAY[CAST(store.id AS VARCHAR), store.name] AS store
        FROM sale_order_line AS sale_line
        LEFT JOIN sale_order AS sale
        ON sale.id = sale_line.order_id
        LEFT JOIN res_partner AS partner
        ON partner.id = sale.partner_id
        LEFT JOIN product_product AS product
        ON product.id = sale_line.product_id
        LEFT JOIN res_partner AS salesdoctor
        ON salesdoctor.id = sale_line.medic_id
        LEFT JOIN product_template AS protemplate
        ON protemplate.id = product.product_tmpl_id
        LEFT JOIN product_category AS category
        ON category.id = protemplate.categ_id
        LEFT JOIN product_attribute_value_product_product_rel AS attr_rel
        ON attr_rel.prod_id = product.id
        LEFT JOIN product_attribute_value AS attr_value
        ON attr_value.id = attr_rel.att_id
        LEFT JOIN product_attribute AS attr
        ON attr.id = attr_value.attribute_id
        LEFT JOIN sale_order_tax AS sale_tax
        ON sale_tax.order_line_id = sale_line.id
        LEFT JOIN account_tax AS tax
        ON tax.id = sale_tax.tax_id
        LEFT JOIN res_users AS users
        ON users.id = sale_line.salesman_id
        LEFT JOIN res_partner AS salesman
        ON salesman.id = users.partner_id
        LEFT JOIN res_company AS company
        ON company.id = sale_line.company_id
        LEFT JOIN stock_warehouse AS warehouse
        ON warehouse.id = sale.warehouse_id
        LEFT JOIN res_store AS store
        ON store.id = warehouse.store_id
        LEFT JOIN product_brand AS brand
        ON brand.id = protemplate.product_brand_id
        WHERE sale_line.state != 'draft'
        GROUP BY
            sale.id,
            sale_line.id,
            sale.name,
            company.id,
            sale.date_order,
            partner.id,
            sale_line.medic_id,
            salesdoctor.name,
            product.name_template,
            brand.id,
            category.id,
            salesman.id,
            store.id
    '''

    r.cr.execute(query)

    return [
        {
            'sale_line_id': j[0],
            'sale_id': j[1],
            'sale_name':j[2],
            'sale_date': j[3],
            'company': j[4],
            'partner': j[5],
            'product_name':j[6],
            'medic_id':j[7],
            'brand':j[8],
            'category':j[9],
            'price_unit': j[10],
            'qty':j[11],
            'discount':j[12],
            'tax':j[13],
            'attribute_rel':j[14],
            'attribute':j[15],
            'attribute_value':j[16],
            'salesman':j[17],
            'store':j[18]

        } for j in r.cr.fetchall()
    ]