You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ofbiz.apache.org by "wulei.bj.cn@gmail.com" <wu...@gmail.com> on 2014/11/18 04:37:36 UTC
回复: Re: GROUP BY month(date)
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 <ja...@hotwaxmedia.com>编写：
>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, wulei.bj.cn@gmail.com <
>wulei.bj.cn@gmail.com> 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智能手机
Re: GROUP BY month(date)
Posted by Jacopo Cappellato <ja...@hotwaxmedia.com>.
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 <ja...@hotwaxmedia.com> 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, wulei.bj.cn@gmail.com 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 <ja...@hotwaxmedia.com>编写：
>>
>
Re: GROUP BY month(date)
Posted by Jacopo Cappellato <ja...@hotwaxmedia.com>.
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, wulei.bj.cn@gmail.com 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 <ja...@hotwaxmedia.com>编写：
>