Find the people whose birthday is today in ofbiz: how to extract month and day from Ofbiz entity

Question

I am interested in finding the people whose birthday is today using ofbiz.

This is the entity for the user:

<entity entity-name="User">
    <field name="identifier" type="id-long"></field>
    <field name="dateOfBirth" type="date"></field>
</entity>

This is the broken code that searches for the users that have birthday today:

SimpleDateFormat monthDayFormat = new SimpleDateFormat("MM-dd");
Calendar cal = Calendar.getInstance();
String today = monthDayFormat.format(cal.getTime());

List<GenericValue> people = dctx.getDelegator().findList("User",
    EntityCondition.makeCondition("dateOfBirth", EntityOperator.LIKE, "%".today),
    null, null, null, false);

This obviously does not work because we are trying to compare a string and a date object.

Another attempt at making it work was to create a view-entity, and either convert the date to two integers: day and month, OR convert the date to a string a use the above code. Obviously, I could not find any way of making it work.

Jacopo Cappellato · Accepted Answer · 2018-03-22T14:56:13.953

The most efficient approach would be that of building a view entity. The following definition should work with several databases (e.g. PostgreSQL), i.e. the ones that support the EXTRACT function:

<view-entity entity-name="UserView">
  <member-entity entity-alias="USER" entity-name="User"/>
  <alias-all entity-alias="USER"/>
  <alias name="dayOfBirth" function="extract-day" entity-alias="USER" field="dateOfBirth"/>
  <alias name="monthOfBirth" function="extract-month" entity-alias="USER" field="dateOfBirth"/>
</view-entity>

With the above view, you could easily perform a query by constraining the two new fields:

List<GenericValue> users = EntityQuery.use(dctx.getDelegator()).from("UserView").where("dayOfBirth", day, "monthOfBirth", month).queryList();

or, if you are coding a Groovy script, its equivalent in OFBiz DSL:

List<GenericValue> users = from("UserView").where("dayOfBirth", day, "monthOfBirth", month).queryList();

Alternatively, following a completely different approach, you could extend your "User" entity by adding the "dayOfBirth" and "monthOfBirth" fields (of type "numeric"):

<entity entity-name="User">
    <field name="identifier" type="id-long"></field>
    <field name="dateOfBirth" type="date"></field>
    <field name="dayOfBirth" type="numeric"></field>
    <field name="monthOfBirth" type="numeric"></field>
</entity>

Then you can define an eca rule to trigger the execution of a service to populate the two new fields every time the User record is create or updated with a non null dateOfBirth field:

<eca entity="User" operation="create-store" event="return">
    <condition field-name="dateOfBirth" operator="is-not-empty"/>
    <action service="populateDayAndMonthOfBirth" mode="sync"/>
</eca>

The service definition for the populateDayAndMonthOfBirth service would look like:

<service name="populateDayAndMonthOfBirth">
    <attribute name="identifier" type="String" mode="IN" optional="false"/>
</service>

(please fill in the missing attributes like "engine", "location" and "invoke"). The service would simply select the record, extract the integers representing the day and month from its dateOfBirth field and would store them in the dayOfBirth and monthOfBirth fields.

This would work on newer versions of Ofbiz; however, my version does not seem to have the `extract-month` and `extract-day` functionality. — Rodolfo, Mar 15 '18 at 16:45
Which version are you using? I can help you to patch your system if you are interested in playing with this feature. — Jacopo Cappellato, Mar 19 '18 at 14:03
I really want to avoid patching the system if at all possible. — Rodolfo, Mar 20 '18 at 13:47
@Rodolfo I have edited my answer by adding an alternative approach that will work in older versions of OFBiz. Please have a look. — Jacopo Cappellato, Mar 22 '18 at 14:57

score 0 · Answer 2 · answered Mar 20 '18 at 07:09

Basically you can combine two condition like:

Calendar cal = Calendar.getInstance();      
cal.setTime(date);                          
cal.set(Calendar.HOUR_OF_DAY, 0);           
cal.set(Calendar.MINUTE, 0);                
cal.set(Calendar.SECOND, 0);                
cal.set(Calendar.MILLISECOND, 0);           
Date tmpDate = cal.getTime();      
Timestamp from = new Timestamp(tmpDate.getTime());
EntityCondition condf = EntityCondition.makeCondition("dateOfBirth", 
EntityOperator.GREATER_THAN_EQUAL_TO, from);

cal.set(Calendar.HOUR_OF_DAY, 23);           
cal.set(Calendar.MINUTE, 59);                
cal.set(Calendar.SECOND, 59);                
cal.set(Calendar.MILLISECOND, 999);           
tmpDate = cal.getTime();  
Timestamp thru = new Timestamp(tmpDate.getTime());
EntityCondition condt = EntityCondition.makeCondition("dateOfBirth", 
EntityOperator.LESS_THAN_EQUAL_TO, thru);

And then find:

List<GenericValue> people = dctx.getDelegator().findList("User",
EntityCondition.makeCondition(condf, condt), null, null, null, false);

This would only return the users who were born today (i.e. this day, this month, *and this year*), a person whose date of birth is in 2017 would not be included in your query. — Rodolfo, Mar 20 '18 at 13:46

Find the people whose birthday is today in ofbiz: how to extract month and day from Ofbiz entity

2 Answers2