View-Entities Group by month

[madppiper-2]

Hey everyone,

If I was to create the following statement:

SELECT ORDER_HEADER.*,MONTH(created_stamp) as month, count(order_id) as ordersum FROM ORDER_HEADER GROUP BY Year(ORDER_HEADER.CREATED_STAMP), Month(ORDER_HEADER.CREATED_STAMP)

Which is basically a simple View on the Order Header data where I group all orders by the month they occured in and the number of orders, how could I create the same within a view-entity where the month() command is not available?

[BJ Freeman]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

the view-entity is basically for grouping entities
then you use a findlist to get the data you want.

madppiper sent the following on 3/4/2009 2:39 AM:

> Hey everyone,
>
> If I was to create the following statement:
>
> SELECT ORDER_HEADER.*,MONTH(created_stamp) as month, count(order_id) as
> ordersum FROM ORDER_HEADER GROUP BY Year(ORDER_HEADER.CREATED_STAMP),
> Month(ORDER_HEADER.CREATED_STAMP)
>
> Which is basically a simple View on the Order Header data where I group all
> orders by the month they occured in and the number of orders, how could I
> create the same within a view-entity where the month() command is not
> available?

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFJrmNZrP3NbaWWqE4RAliUAJ9NtEm3imCmpMwgtJjPqOhKUmkhVwCfeu9m
rOdxGvqPsEIyapbbHRf3tVc=
=yQhF
-----END PGP SIGNATURE-----

[madppiper-2]

Ah,

well, I got myself around that problem by a simple groovy script. I wanted to share the result in case that anybody else got the problem.



Note that the following approach is a fall-back to the classical SQL - commands and may therefore NOT be recommended!



---
String sqlCommand = "SELECT * FROM Products";
records = [];

String helperName = delegator.getGroupHelperName("org.ofbiz");    // gets the helper (localderby, localmysql, localpostgres, etc.) for your entity group org.ofbiz
SQLProcessor sqlproc = new SQLProcessor(helperName);
rs = sqlproc.executeQuery(sqlCommand);
                                       
records = [];
numberOfColumns = rs.getMetaData().getColumnCount();

  while (rs.next()) {
                    record = [:];
                    rs.getMetaData()
                    for (i = 1; i <= numberOfColumns; i++) {
                    record.put(rs.getMetaData().getColumnName(i),rs.getObject(i));
                    }
                    records.add(record);
                }

context.put("sqlresult",records);
----


The above script should call any of your sql-statements in sqlCommand and return the results in a matter that can be easily processed by the freemarker templates (ftl files). The result is similar to any result given by a regular delegator call, so you can access the data in your ftl file via ${sqlresult.columnname}


Cheers,
Paul