0036024: Error in Best Selling Store widget - Openbravo Issue Tracking System

Openbravo Issue Tracking System - Retail Modules

View Issue Details

Project

Category

View Status

Date Submitted

Last Update

0036024

Retail Modules

Retail Widgets

public

2017-05-17 13:24

2017-05-17 13:30

Reporter

aferraz

Assigned To

Retail

Priority

normal

Severity

minor

Reproducibility

have not tried

Status

new

Resolution

open

Platform

OS Version

Professional Appliance

Product Version

Target Version

Fixed in Version

Merge Request Status

Review Assigned To

OBNetwork customer

Support ticket

Regression level

Regression date

Regression introduced in release

Regression introduced by commit

Triggers an Emergency Pack

Summary

0036024: Error in Best Selling Store widget

Description

Error in Best Selling Store widget

Steps To Reproduce

Run Best Selling Store widget.

Following exception is raised:

Exception while fetching the summary columns of the widget Best Selling Store. It is not supported using as summaries columns that are defined using a subquery, or that are defined using a summary function.
Query = select sum(quantity), sum(totalsales), sum(totaldiscount), sum(totaldiscpercentage)/count(*), sum(totalcost), sum(markup)/count(*), sum(grossmargin), sum(grossmarginpercentage)/count(*) from OrderLineTax olt where olt.salesOrderLine = sol)),2) as totalsales,
round(sum(tc.cost),2) as totalcost,
case when round(sum(tc.cost),2)>0 then round(sum(sol.lineNetAmount + (select sum(taxAmount) from OrderLineTax olt where olt.salesOrderLine = sol))/(sum(tc.cost)),2) else NULL end as markup,
round(sum(sol.lineNetAmount + (select sum(taxAmount) from OrderLineTax olt where olt.salesOrderLine = sol)-tc.cost),2) as grossmargin,
round((sum(sol.lineNetAmount + (select sum(taxAmount) from OrderLineTax olt where olt.salesOrderLine = sol)-tc.cost)/sum(sol.lineNetAmount + (select sum(taxAmount) from OrderLineTax olt where olt.salesOrderLine = sol)))*100,2) as grossmarginpercentage
from TransactionCost as tc
join tc.inventoryTransaction as t
right join t.goodsShipmentLine as iol
left join iol.salesOrderLine as sol
left join sol.salesOrder as o
left join o.obposApplications as posapp
left join o.organization as org
where o.salesTransaction = 'Y'
and o.documentStatus = 'CO'
and posapp.client.id=:client
and posapp.organization.id IN (:organizationList)
and to_date(to_char(o.creationDate, 'YYYY-MM-DD'), 'YYYY-MM-DD')>=to_date(:datefrom,'YYYY-MM-DD')
and to_date(to_char(o.creationDate, 'YYYY-MM-DD'), 'YYYY-MM-DD')<=to_date(:dateto,'YYYY-MM-DD')
and 1=1
group by org.name, org.id

Proposed Solution

Additional Information