Open GoogleCodeExporter opened 8 years ago
Postponed
Original comment by harald.g...@gmail.com
on 31 Aug 2012 at 10:23
jasper reports are created from the DB data
- see JPanelReport.java - launchreport() - line 156 ->
JasperFillManager.fillReport()
- see \Fairpos-src\netbeans\build\classes\com\openbravo\reports\products.bs
possible solution of this Issue 80: alternate SQL query in products.bs, so it
returns as article price this sum: article price + sum of all of its attached
items
other solution: create separate report for hampers - so also separate
table/view in DB with hampers and their (non-zero) prices (which would need to
be calculated from attached items by OpenBravoPOS app just before the creation
of the report)
----
SQL example:
For example, you could also use the SUM function to return the name of the
department and the total sales (in the associated department).
SELECT department, SUM(sales) as "Total sales"
FROM order_details
GROUP BY department;
Original comment by benes.vi...@gmail.com
on 3 Oct 2012 at 9:25
SQL query which gives sum of pricesell of atttached non-auxiliary products for
each product is:
"""
select P.NAME, SUM(A.PRICE_ATTACHED)
from (SELECT P.NAME as XXX, M.PRODUCT, P.ID, P.REFERENCE, P.ISCOM, P.PRICESELL
as PRICE_ATTACHED FROM PRODUCTS P, PRODUCTS_CAT O, PRODUCTS_COM M WHERE P.ID =
O.PRODUCT AND P.ISCOM = False AND P.ID = M.PRODUCT2) as A
LEFT JOIN PRODUCTS P ON P.ID = A.PRODUCT
GROUP BY P.NAME
"""
...using this, we can create separate report for hampers
----
More detail:
See DataLogicSales.java - getAttachedNonauxiliary() for SQL query to get
attached non-auxiliary items... This was adapted (see the 'select' inside
referenced as A, see the 'ON' condition.
To prevent listing hampers in (non-hamper) report, something like this may work
(we need products which are not referenced from attached items table):
http://stackoverflow.com/questions/4076098/how-to-select-rows-with-no-matching-e
ntry-in-another-table
Original comment by benes.vi...@gmail.com
on 15 Oct 2012 at 10:12
[deleted comment]
We need this one now...
But it can wait until I have finished the label printing, because then we have
the correctly formated report to print with the label printer.
Original comment by harald.g...@gmail.com
on 18 Jan 2013 at 8:55
Ok. I have the solution - but it requires a bit of manual messing with the
files.
----
*Part I/II* - In SQL, we create 'view_pruducts_hampers' using this SQL command:
"""
create view view_products_hampers as
select P.NAME, SUM(A.PRICE_ATTACHED) AS PRICESELL, SUM(A.PRICE_ATTACHED_BUY) AS
PRICEBUY, P.ID, P.REFERENCE, P.CODE, P.CATEGORY, P.TAXCAT
from (
SELECT P.NAME as XXX,
M.PRODUCT, P.ID, P.REFERENCE, P.ISCOM,
P.PRICESELL as PRICE_ATTACHED, P.PRICEBUY as PRICE_ATTACHED_BUY FROM PRODUCTS
P,
PRODUCTS_CAT O, PRODUCTS_COM M
WHERE P.ID = O.PRODUCT
AND P.ISCOM = False AND P.ID = M.PRODUCT2
) as A
LEFT JOIN PRODUCTS P ON P.ID = A.PRODUCT
GROUP BY P.NAME, P.ID, P.REFERENCE, P.CODE, P.PRICEBUY, P.PRICESELL,
P.CATEGORY, P.TAXCAT
"""
(0. open pgAdmin III; 1. click icon with magnifier glass with 'SQL' label (&
hint: "Execute arbitrary SQL queries"); 2. enter given 'create view' SQL
command; 3. hit F5)
----
*Part II/II* - In FairPOS, we use Jasper reports for hampers.
This is done by switching report "Products" / report "Product labels" to use
'view_products_hampers' view instead of 'products' table.
See (these files are only for illustration, they are not used by openbravo
itself):
https://fairpos.googlecode.com/svn/trunk/OpenbravoPOS/reports/com/openbravo/repo
rts/products-hampers.bs
https://fairpos.googlecode.com/svn/trunk/OpenbravoPOS/reports/com/openbravo/repo
rts/productlabels-hampers.bs
...this is how "products.bs" & "productlabels.bs" needs to be to create hamper
reports (just 'PRODUCTS' was replaced everywhere with 'view_products_hampers')
----
@Harald: with this, you can print labels for both products & hampers (hampers
after manual change in FairPOS filestructure which will take 1 min). I was
trying to create everything (separate .bs files for hampers, separate buttons)
but I got stuck with some permissions. I can extend it later...
Original comment by benes.vi...@gmail.com
on 22 Jan 2013 at 7:04
Attachments:
Looks good, thanks a lot!
So we can quickly finish this when I got the "real" reports running. Currently
stuck with problems with the label printer...
Original comment by harald.g...@gmail.com
on 22 Jan 2013 at 8:49
Original issue reported on code.google.com by
harald.g...@gmail.com
on 31 Jul 2012 at 9:59