回复: Re: GROUP BY month(date)

[Lei Wu]

Dear Jacopo,

Thanks so much for your kind help, the solution works perfectly as it is exactly what I wanted ! Now I can summary the total in the dimensions I want, i.e. MONTH and YEAR. Thanks so much again, and I also think it is helpful to commit these two added functions to public trunk in case others have similar requirements like mine.

--
发自我的ONEPLUS智能手机

Jacopo Cappellato <[hidden email]>编写：

>month is not a standard function of SQL even if nowadays is available in
>most database management systems.
>However you may find it useful the patch below that adds the support for
>the date and month functions (if others are interested it could be
>committed to public repository).
>In order to use it you will have to define an alias field like:
>
><alias entity-alias="EX" name="trx_date" function="month"/>
>
>I hope it helps,
>
>Jacopo
>
>Index:
>../trunk/framework/entity/src/org/ofbiz/entity/model/ModelViewEntity.java
>
>===================================================================
>
>---
>../trunk/framework/entity/src/org/ofbiz/entity/model/ModelViewEntity.java
>(revision
>1637645)
>
>+++
>../trunk/framework/entity/src/org/ofbiz/entity/model/ModelViewEntity.java
>(working
>copy)
>
>@@ -65,6 +65,8 @@
>
>         functionPrefixMap.put("count-distinct", "COUNT(DISTINCT ");
>
>         functionPrefixMap.put("upper", "UPPER(");
>
>         functionPrefixMap.put("lower", "LOWER(");
>
>+        functionPrefixMap.put("year", "YEAR(");
>
>+        functionPrefixMap.put("month", "MONTH(");
>
>     }
>
>
>
>     /** Contains member-entity alias name definitions: key is alias, value
>is ModelMemberEntity */
>
>@@ -480,7 +482,7 @@
>
>                     fieldSet = alias.getFieldSet();
>
>                 }
>
>             }
>
>-            if ("count".equals(alias.function) ||
>"count-distinct".equals(alias.function)) {
>
>+            if ("count".equals(alias.function) ||
>"count-distinct".equals(alias.function) || "year".equals(alias.function) ||
>"month".equals(alias.function)) {
>
>                 // if we have a "count" function we have to change the type
>
>                 type = "numeric";
>
>             }
>
>
>On Sat, Nov 15, 2014 at 8:19 AM, [hidden email] <
>[hidden email]> wrote:
>
>> Guys,
>>
>> I have an Expense entity with a trx_date colum of type DATE to record the
>> date that I bought sth.
>> And then I wanna summary the total expenses by month, and I hit a blocker.
>> I tried to create an view entity with a complex alias to represent the
>> month, it's like:
>> <alias name="month" group-by="true">
>>   <complex-alias operator="month()">
>>     <complex-alias-field entity="Expense" field="trx_date">
>>   </complex-alias>
>> </alias>
>>
>> However, it did not work. Could anyone help me out here to get what I
>> wanted? Thanks so much!
>>
>> I am using the default database derby by the way. And Ofbiz version is
>> 12.04.
>> --
>> 发自我的ONEPLUS智能手机

[Jacopo Cappellato-4]

Thanks for letting me know it worked for you.
I will commit it sometime soon if no one will object.

Best regards,

Jacopo

On Nov 18, 2014, at 4:37 AM, [hidden email] wrote:

> Dear Jacopo,
>
> Thanks so much for your kind help, the solution works perfectly as it is exactly what I wanted ! Now I can summary the total in the dimensions I want, i.e. MONTH and YEAR. Thanks so much again, and I also think it is helpful to commit these two added functions to public trunk in case others have similar requirements like mine.
>
> --
> 发自我的ONEPLUS智能手机
>
>
>
> Jacopo Cappellato <[hidden email]>编写：
>

[Jacopo Cappellato-4]

It would be great if you could test this improved version of the patch that I sent you: it internally uses the EXTRACT function that should be a standard function since SQL-99.

Thanks,

Jacopo


Index: framework/entity/src/org/ofbiz/entity/model/ModelViewEntity.java
===================================================================
--- framework/entity/src/org/ofbiz/entity/model/ModelViewEntity.java (revision 1641394)
+++ framework/entity/src/org/ofbiz/entity/model/ModelViewEntity.java (working copy)
@@ -65,6 +65,8 @@
         functionPrefixMap.put("count-distinct", "COUNT(DISTINCT ");
         functionPrefixMap.put("upper", "UPPER(");
         functionPrefixMap.put("lower", "LOWER(");
+        functionPrefixMap.put("year", "EXTRACT(YEAR FROM ");
+        functionPrefixMap.put("month", "EXTRACT(MONTH FROM ");
     }
 
     /** Contains member-entity alias name definitions: key is alias, value is ModelMemberEntity */
@@ -480,7 +482,7 @@
                     fieldSet = alias.getFieldSet();
                 }
             }
-            if ("count".equals(alias.function) || "count-distinct".equals(alias.function)) {
+            if ("count".equals(alias.function) || "count-distinct".equals(alias.function) || "year".equals(alias.function) || "month".equals(alias.function)) {
                 // if we have a "count" function we have to change the type
                 type = "numeric";
             }


On Nov 18, 2014, at 11:40 AM, Jacopo Cappellato <[hidden email]> wrote:

> Thanks for letting me know it worked for you.
> I will commit it sometime soon if no one will object.
>
> Best regards,
>
> Jacopo
>
> On Nov 18, 2014, at 4:37 AM, [hidden email] wrote:
>
>> Dear Jacopo,
>>
>> Thanks so much for your kind help, the solution works perfectly as it is exactly what I wanted ! Now I can summary the total in the dimensions I want, i.e. MONTH and YEAR. Thanks so much again, and I also think it is helpful to commit these two added functions to public trunk in case others have similar requirements like mine.
>>
>> --
>> 发自我的ONEPLUS智能手机
>>
>>
>>
>> Jacopo Cappellato <[hidden email]>编写：
>>
>