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 ?
