ofbiz-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jacques Le Roux <jacques.le.r...@les7arts.com>
Subject Re: GROUP BY month(date)
Date Sun, 16 Nov 2014 17:06:41 GMT
Yes, why not committing it?

Jacques

Le 15/11/2014 09:29, Jacopo Cappellato a écrit :
> 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智能手机

Mime
View raw message