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

Business Intelligence view bug / error

By
Erhuvwu Akpobaro
on 7/7/15, 11:54 AM 572 views


 Hello All ,

I am Currently having issues with a BI view I have created . The view uses a three part SQL union (so each pulling in a lof of records )

The problem is when I look  at values in the view for example by customer (res.partner ) I get empty cell with no values , however in my SQL there is values for example sorting invoices by month , everything shows up correctly but one month will be randomly empty if I expand then the data shows .


however what i tried is to search for the customer is using the search view the right customer comes in the search bar but a random user appears that has no relation whatsoever.


The image above further illustrates the issue at hand .


when i run the query at the command line i don ot get any mismatch results , the right invoice /journal reference is assigned to the right partner but as soon as i put the query in the view it produces situation like the above :


code.py:


class mm_market_arrears_report(osv.osv):

_name = "mm.market.arrears.report"

_description = "mm_market_arrears_report"

_auto = False

_columns = {

'id': fields.integer('id'),

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

'partner': fields.many2one('res.partner', 'Tenant',readonly=True),

'journal_ref': fields.char('Journal ref', size=64, readonly=True),

'transaction_source': fields.char('Transaction Source'),

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

'invcompany_id':fields.many2one('res.company', 'Property Company', required=True),

'journal_type': fields.char('Journal'),

'debit': fields.float('Debit'),

'credit': fields.float('Credit'),

'balance': fields.float('Balance')

}

def init(self, cr):

tools.drop_view_if_exists(cr, 'mm_market_arrears_report')

cr.execute("""

create or replace view mm_market_arrears_report as (

SELECT --DISTINCT am.id as id,

10000 + am.id as id ,

aaa2.invcompany_id as invcompany_id,

am.date as date,

am.company_id as company_id,

aml2.partner_id as partner,

am.name as journal_ref,

j.name as journal_type,

'inv' as transaction_source , -- transaction type for sales cmmc invoices

( select aml.debit from account_move_line aml WHERE aml.move_id = aml2.move_id and aml.account_id = 1734 ) AS debit , -- lookup for debit --

( select aml.credit from account_move_line aml WHERE aml.move_id = aml2.move_id and aml.account_id = 1734 ) AS credit , -- lookup for credit ---

sum(credit - debit) as balance -- sum of debit , credit

FROM account_analytic_account aaa2

LEFT JOIN account_move_line aml2

ON aml2.analytic_account_id = aaa2.id

LEFT JOIN account_move am

ON (am.id=aml2.move_id)

LEFT JOIN account_journal j

ON (aml2.journal_id = j.id)

WHERE aaa2.invcompany_id IS NOT NULL

AND am.name IS NOT NULL

AND aaa2.state = 'open'

--AND am.state = 'posted' --- making sure status journal entries has beeen posted

AND debit IS NOT NULL

and aml2.state = 'valid'

GROUP BY am.id ,aaa2.invcompany_id ,aml2.partner_id ,

aml2.move_id , am.name , j.name

UNION -- union seperator ---

SELECT --am.id as id ,

20000+ am.id as id ,

( SELECT DISTINCT

aaa2.invcompany_id

FROM account_analytic_account aaa2

LEFT JOIN account_move_line aml2

ON aml2.analytic_account_id = aaa2.id

WHERE aml2.partner_id = aml.partner_id

AND aml2.move_id = aml.move_id

) as invcompany_id , -- look up for invoicing company --

am.date as date,

av.company_id ,

aml.partner_id as partner,

av.number as journal_ref,

j.name as journal_type,

'cbc' as transaction_source , -- transaction type for Cash , Bank and Cheque Transfers

aml.credit as debit, -- reverse needed as the cheque , bank , cash is paying of it reverse invoice journal

aml.debit as credit, -- reverse needed as the cheque , bank , cash is paying of it reverse invoice journal

sum(credit-debit) as balance -- sum of debit ,credit

FROM account_voucher av

LEFT JOIN account_voucher_line avl on (avl.voucher_id = av.id)

LEFT JOIN account_move_line aml on ( avl.move_line_id = aml.id)

LEFT JOIN account_move am on ( am.id = aml.move_id)

LEFT JOIN account_journal j

ON (aml.journal_id = j.id)

LEFT JOIN account_analytic_account aaa1

ON (aml.analytic_account_id = aaa1.id and aaa1.state = 'open' )

WHERE exists (

SELECT DISTINCT

aaa2.invcompany_id

FROM account_analytic_account aaa2

LEFT JOIN account_move_line aml2

ON aml2.analytic_account_id = aaa2.id

WHERE aml2.partner_id = aml.partner_id

AND aml2.move_id = aml.move_id

AND aaa2.state = 'open'

)

AND reconcile = 't' -- key filter as we only need reconcile payments --

AND av.state = 'posted'

AND am.state = 'posted'

GROUP BY am.id ,aml.partner_id , av.number , av.company_id ,debit , credit ,

aml.move_id , j.name

UNION -- union seperator ---

Select

30000 + l.id as id ,

aaa. invcompany_id ,

s.date_order as date,

aaa.company_id as company_id,

s.partner_id as partner,

s.name as journal_ref ,

j.name as journal_type ,

'pos' as transaction_source , -- transaction type for all pos cash --

0 as debit, -- debit set to 0 as POS is only credit value -- ,

sum(l.qty * l.price_unit) as credit , -- pos_order_line sum of credit,

sum (0 - (l.qty * l.price_unit)) as balance --- caluclation of 0 because debit is always 0 against the pos_order_line credit

-- l.product_id as product_id ,

from pos_order_line as l

left join pos_order s on (s.id=l.order_id)

left join product_product p on (p.id=l.product_id)

left join product_template pt on (pt.id=p.product_tmpl_id)

left join product_uom u on (u.id=pt.uom_id)

Left join account_journal j on (j.id = s.sale_journal)

left outer join account_analytic_invoice_line aail on aail.product_id = l.product_id

left outer join account_analytic_account aaa on aail.analytic_account_id = aaa.id

WHERE s.state = 'done' --- making sure status pos has beeen posted --

and pt.name NOT LIKE 'Electric%' --- to remove electric card as this is a one of payment

and aaa.state = 'open'

group by

aaa.invcompany_id ,aaa.company_id , s.partner_id,s.date_order ,s.sale_journal,l.id ,s.id ,j.name ,p.id

)

""")



code.xml :


<openerp>

<data>

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

<field name="name">mm.market.arrears.report.search</field>

<field name="model">mm.market.arrears.report</field>

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

<search string="Market Manager Arrears Report">

<group expand="0" string="Group By">

<filter string="Tenant" context="{'group_by':'partner'}"/>

<filter string="Journal Reference " name="Journal Reference" context="{'group_by':'journal_ref'}"/>>

<filter string="Property Company " name="Property_company" context="{'group_by':'invcompany_id'}"/>

<filter string="Company " name="Company" context="{'group_by':'company_id'}"/>

<filter string="Transaction Source" name="Transaction Source" context="{'group_by':'transaction_source'}"/>

<separator/>

<filter string="Month" name="Month" context="{'group_by':'date:month'}"/>

</group>

</search>

</field>

</record>


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

<field name="name">mm.market.arrears.report.graph</field>

<field name="model">mm.market.arrears.report</field>

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

<graph string="Market Manager Market Manager Arrears Report" type="pivot">

<field name="invcompany_id" type="row"/>

<field name="debit" type="measure"/>

<field name="credit" type="measure"/>

<field name="balance" type="measure"/>

</graph>

</field>

</record>


<record id="action_market_arrears_report" model="ir.actions.act_window">

<field name="name"> Revenue Analysis Report</field>

<field name="res_model">mm.market.arrears.report</field>

<field name="view_type">form</field>

<field name="view_mode">graph</field>

<field name="search_view_id" ref="view_mm_market_arrears_report_search"/>

<field name="context">{'search_default_current':1, 'search_default_customer':1, 'group_by':[], 'group_by_no_leaf':1, 'search_default_year': 1}</field>

<field name="help">From this view, have an analysis of your different analytic entries following the analytic account you defined matching your business need. Use the tool search to analyse information about analytic entries generated in the system.</field>

</record>

<menuitem action="action_market_arrears_report"

id="menu_action_saction_market_arrears_report"

groups="analytic.group_analytic_accounting"

parent="account.menu_finance_reporting" sequence="4"/>

<menuitem action="action_market_arrears_report" id="cm_menu_market_arrears_report_tree"

parent="menu_management_dashboard_root"/>

</data>

</openerp>



any ideas as to how this issue can be resolved ?

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: 7/7/15, 11:54 AM
Seen: 572 times
Last updated: 7/7/15, 11:54 AM