Search code examples
javaofbizdate-comparisondate

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

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.

Solution

  • 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.