Unfortunately, my whole display page is in Form Widget with "Form Type =
list", So I think the best way for this case is to have Query with sum().
Correct me if I am wrong. Because at this point, I am not sure whether I can
convert my entire Form widget into Freemarker.
Thank you for your help.
Su-
You can correct me if I am wrong.
Chris Snow-3 wrote:
>
> 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-
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
>
--
View this message in context:
http://www.nabble.com/is-Stroed-Procedures-possible-in-OFBiz--tp25768161p25769827.html
Sent from the OFBiz - User mailing list archive at Nabble.com.
