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.

-2

SQL code

By
Jihen BEN ALI
on 8/24/15, 6:01 PM 378 views

Hi everyone,

I have this code below which give me the receipt_amount and the payment_amount:

from openerp import tools

from openerp.osv import fields, osv

class caisse_base_report(osv.osv):

_name = 'caisse.base.report'

_auto = False

_rec_name = 'date_confirm'

_columns = {

'date_confirm': fields.date('Date', readonly=True),

'receipt_amount_change': fields.float('Receipt Amount Change', readonly=True),

'payment_amount_change': fields.float('Payment Amount Change', readonly=True),

}

_order = 'date_confirm desc'

def _select(self):

select_str = """

SELECT min(l.id) as id,

CASE

WHEN l.type= 'receipt'

THEN l.amount

ELSE 0

END AS receipt_amount_change,

CASE

WHEN l.type= 'payment'

THEN l.amount

ELSE 0

END AS payment_amount_change,

l.date as date_confirm

"""

return select_str

def _from(self):

from_str = """

account_voucher l

left join

account_voucher_line a on (l.account_id=a.id)

"""

return from_str

def _group_by(self):

group_by_str = """

GROUP BY l.id

"""

return group_by_str

def init(self, cr):

# self._table = caisse_base_report

tools.drop_view_if_exists(cr, self._table)

cr.execute("""CREATE or REPLACE VIEW %s as (

%s

FROM ( %s )

%s

)""" % (self._table, self._select(), self._from(), self._group_by()))

class caisse_report(osv.osv):

_name = 'caisse.report'

_auto = False

_rec_name = 'date_confirm'

_columns = {

'date_confirm': fields.date('Date', readonly=True),

'receipt_amount_change': fields.float('Receipt Amount Change', readonly=True),

'payment_amount_change': fields.float('Payment Amount Change', readonly=True),

'receipt_amount': fields.float('Montant dentrée', readonly=True),

'payment_amount': fields.float('Montant de sortie', readonly=True),

}

_order = 'date_confirm desc'

def _select(self):

select_str = """

SELECT min(l.id) as id,

payment_amount_change,

receipt_amount_change,

CASE

WHEN l.type= 'receipt'

THEN l.amount

ELSE 0

END AS receipt_amount,

CASE

WHEN l.type= 'payment'

THEN l.amount

ELSE 0

END AS payment_amount,

l.date as date_confirm

"""

return select_str

def _from(self):

from_str = """

account_voucher l

join caisse_base_report c on (l.id=c.id)

left join

account_voucher_line a on (l.account_id=a.id)

"""

return from_str

def _group_by(self):

group_by_str = """

GROUP BY l.id,

receipt_amount_change,

payment_amount_change

"""

return group_by_str

def init(self, cr):

# self._table = caisse_report

tools.drop_view_if_exists(cr, self._table)

cr.execute("""CREATE or REPLACE VIEW %s as (

%s

FROM ( %s )

%s

)""" % (self._table, self._select(), self._from(), self._group_by()))

The xml file:

<record model="ir.ui.view" id="view_caisse_tree">

<field name="name">caisse.report</field>

<field name="model">caisse.report</field>

<field name="type">tree</field>

<field name="arch" type="xml">

<tree string="Caisse">

<field name="date_confirm"/>

<field name="receipt_amount"/>

<field name="payment_amount"/>

</tree>

</field>

</record>

My problem is that the sum doesn't work because I want to display the sum amount of each type(receipt and payment) also I want to display this sum at the same line because in my case it gives me the receipt_amount and in the second line it gives me the payment_amount and I want the payment_amount to be set in the next column of the receipt_amount( not displaying the zero)

Any help please.

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: 8/24/15, 6:01 PM
Seen: 378 times
Last updated: 8/25/15, 1:06 AM