Re: is Stroed Procedures possible in OFBiz?

[Chris Snow]

I wasn't aware that list forms can display lists with detail and summary 
level data...

su2 wrote:
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-