One method (not necessarily the best) is to output the list in
freemarker. You can assign a variable in freemarker to the value you
are summing.
<#assign totalAmount = 0>
<table>
<#list context.detailList as row>
<tr>
<td>${row.itemDescription}</td>
<td>${row.itemAmount}</td>
</tr>
<#assign totalAmount = totalAmount + row.itemAmount>
</#list>
<tr>
<td>TOTAL</td>
<td>${totalAmount}</td>
</tr>
su2 wrote:
Hi Chris,
It is a list/result of multiple table joins.
I have requirement of displaying both aggregated (summed) values as well as
to display the detail (unsummed) records.
I have little idea to get detail/unsummed record as list using <form
type=list>. But I do not have any clue how to use sum(column name) with the
java delegator.
I have following simple Query - SELECT with WHERE clause. But I need to have
SUM(OTCMAdjustmentAmount). (Here OrderAndTax is a view-entity created using
two other view-entities.)
--------------------------------------------------------------------------------------
List fieldsToSelect = UtilMisc.toList("OTCMAdjustmentAmount");
EntityConditionList whereConditions = new
EntityConditionList(UtilMisc.toList(
new EntityExpr("OTCMOrderNo", EntityOperator.EQUALS,
OTCMOrderNo),
new EntityExpr("OTCMAdjustmentAmountType",
EntityOperator.EQUALS, "SALES_TAX")
), EntityOperator.AND);
orderTaxObject = delegator.findByCondition("OrderAndTax",
whereConditions, null, fieldsToSelect, null, null);
--------------------------------------------------------------------------------------
I really appreciate your help.
Thank you.
Su-
Chris Snow-3 wrote:
Hi Su, how are you using the returned data? Is it a table list, or just
a single form?
If it is a list, do you want to display just the aggregated (summed)
values, or do you want to display the detail (unsummed) records too?
su2 wrote:
Hi Chris,
Thank you for the response.
My query has multiple joins with different tables and also I would like
to
use sum(column name) and (column 1 + column 2) as column 3.
Is it possible to use sum(column name) in java method with delegator?
Also, the stored procedure I want to use in jasper report.
Thank you for the help.
Su-
Chris Snow-3 wrote:
Hi Su,
I use SQL Views for complex queries:
<entity entity-name="ClearanceReport"
table-name="vClearanceReport"
package-name="uk.co.dhales"
title="Clearance Report Entity"
no-auto-stamp="true"
never-cache="true"
>
<field name="strIsrCompany" col-name="strIsrCompany"
type="long-varchar"/>
<field name="strIsrBranch" col-name="strIsrBranch"
type="long-varchar"/>
<field name="strIsrCode" col-name="strIsrCode"
type="long-varchar"/>
<field name="ingUplNumber" col-name="ingUplNumber"
type="numeric"/>
...
<prim-key field="ingUplNumber"/>
</entity>
The table name vClearanceReport is actually a SQL View. The attributes
no-auto-stamp and never-cache where needed.
Also, here is a service that is using a SQL Stored procedure:
public class DHSequenceService {
public static Map getNextSequenceValue(DispatchContext ctx, Map
context) {
Connection conn = null;
CallableStatement statement = null;
String sql = "{call getNextSequenceValue(?,?)}";
Long seqValue;
try{
conn = ConnectionFactory.getConnection("dhmssql");
if (conn == null) {
throw new Exception("No dhmssql connection
configured");
}
statement = conn.prepareCall (sql);
String seqTable = (String)context.get("seqTable");
statement.registerOutParameter(1, Types.INTEGER);
statement.setString(2, seqTable);
statement.execute();
seqValue = new Long(statement.getLong(1));
statement.close();
} catch (Exception e){
Debug.logError(e, "Error running SQL - ",
DHSequenceService.class.getName());
return ServiceUtil.returnError("Error running SQL" + e);
}
finally {
if (statement != null)
try {
statement.close();
} catch (Exception e) {}
if (conn != null)
try {
conn.close();
} catch (Exception e) {}
}
Map resultMap = ServiceUtil.returnSuccess();
resultMap.put("seqValue", seqValue);
return resultMap;
}
}
Note that by using SQL Views/Stored Procedures you are limiting the
portability of your application to other databases.
Cheers,
Chris
su2 wrote:
Hello Friends,
I need to write quite complex query which is little easier to write as
Stored Procedures. So I would like to know whether is it possible to
write
and access stored procedures in OFBiz with mySQL?
If its possible, can i have example or reference for how to do that ?
Thank you for the help in advance.
Su-
