This discussion is archived
9 Replies Latest reply: Oct 30, 2013 2:22 AM by fac586 RSS

Events in calendar formatting

LathaVinnakota Newbie
Currently Being Moderated

I have a calendar created using APEX. I am using the following SQL/HTML code to get the format I have

 

select    VIEW_EVENT_CALENDAR_MASTER_ACT.ACTIVITY_ID as ACTIVITY_ID,

  VIEW_EVENT_CALENDAR_MASTER_ACT.EVENT_DATES_ID AS EVENT_DATES_ID,

  VIEW_EVENT_CALENDAR_MASTER_ACT.EVENT_ID as EVENT_ID,

  VIEW_EVENT_CALENDAR_MASTER_ACT.EMPLOYEE_ID as EMPLOYEE_ID,

  VIEW_EVENT_CALENDAR_MASTER_ACT.DATE_FROM as DATE_FROM,

  VIEW_EVENT_CALENDAR_MASTER_ACT.DATE_TO as DATE_TO,

  VIEW_EVENT_CALENDAR_MASTER_ACT.DUE_DATE as DUE_DATE,

          VIEW_EVENT_CALENDAR_MASTER_ACT.ON_EVENT_DATE AS ON_EVENT_DATE,

  VIEW_EVENT_CALENDAR_MASTER_ACT.ACT_LOC as ACT_LOC,

          VIEW_EVENT_CALENDAR_MASTER_ACT.DIVISION as DIVISION,

          VIEW_EVENT_CALENDAR_MASTER_ACT.ACT_DESC as ACT_DESC,

'<font color ="Blue">'||

          ACT_DESC ||' - '||

          (SELECT FULL_NAME FROM EMPLOYEE

          WHERE EMPLOYEE_NUMBER = VIEW_EVENT_CALENDAR_MASTER_ACT.EMPLOYEE_ID) || '</font>' SHOW

          from "VIEW_EVENT_CALENDAR_MASTER_ACT"

 

Above code gets me

 

Vacation - XXXX.

 

It works good for one employee. If I have more than one employee on the same day. I can see it as

Vacation - XXXX.

Vacation  - YYYY.

Vacation - ZZZZ.

 

I am looking for a format like

Vacation - XXXX.

                YYYY.

                ZZZZ.

or

Vacation -

XXXX.

YYYY.

ZZZZ.

 

Thanks for your help in advance

 

Thanks

Latha

  • 1. Re: Events in calendar formatting
    fac586 Guru
    Currently Being Moderated

    LathaVinnakota wrote:

     

    I have a calendar created using APEX.

    APEX version?

    Database version?

    Theme?

     

    (Always include this information when posting a question on the forum).

     

    Do you mean an APEX Calendar region? Or another type of region that you are using to display "calendar" data? This query looks over complex for a Calendar region source.

     

    I am using the following SQL/HTML code to get the format I have

     

    select    VIEW_EVENT_CALENDAR_MASTER_ACT.ACTIVITY_ID as ACTIVITY_ID,

      VIEW_EVENT_CALENDAR_MASTER_ACT.EVENT_DATES_ID AS EVENT_DATES_ID,

      VIEW_EVENT_CALENDAR_MASTER_ACT.EVENT_ID as EVENT_ID,

      VIEW_EVENT_CALENDAR_MASTER_ACT.EMPLOYEE_ID as EMPLOYEE_ID,

      VIEW_EVENT_CALENDAR_MASTER_ACT.DATE_FROM as DATE_FROM,

      VIEW_EVENT_CALENDAR_MASTER_ACT.DATE_TO as DATE_TO,

      VIEW_EVENT_CALENDAR_MASTER_ACT.DUE_DATE as DUE_DATE,

              VIEW_EVENT_CALENDAR_MASTER_ACT.ON_EVENT_DATE AS ON_EVENT_DATE,

      VIEW_EVENT_CALENDAR_MASTER_ACT.ACT_LOC as ACT_LOC,

              VIEW_EVENT_CALENDAR_MASTER_ACT.DIVISION as DIVISION,

              VIEW_EVENT_CALENDAR_MASTER_ACT.ACT_DESC as ACT_DESC,

    '<font color ="Blue">'||

              ACT_DESC ||' - '||

              (SELECT FULL_NAME FROM EMPLOYEE

              WHERE EMPLOYEE_NUMBER = VIEW_EVENT_CALENDAR_MASTER_ACT.EMPLOYEE_ID) || '</font>' SHOW

              from "VIEW_EVENT_CALENDAR_MASTER_ACT"

     

    Above code gets me

     

    Vacation - XXXX.

     

    It works good for one employee. If I have more than one employee on the same day. I can see it as

    Vacation - XXXX.

    Vacation  - YYYY.

    Vacation - ZZZZ.

     

    I am looking for a format like

    Vacation - XXXX.

                    YYYY.

                    ZZZZ.

    or

    Vacation -

    XXXX.

    YYYY.

    ZZZZ.

    I would suggest that the best way to get assistance with this is to create a working example of your current page on apex.oracle.com and share guest developer credentials to the workspace here.

  • 2. Re: Events in calendar formatting
    LathaVinnakota Newbie
    Currently Being Moderated

    Apex version is 4.1 and database is 11g. I have created a workspace in apex.oracle.com.

    workspace: Latha

    username: nvinnakota@cpsenergy.com

    password:latha

    Application: 59619

     

    You can see the issue on date november 25.

     

    Thanks

    Latha

  • 3. Re: Events in calendar formatting
    fac586 Guru
    Currently Being Moderated

    There are a couple of problems with trying to meet this requirement in a Calendar region. Events displayed in Calendar cell for a single day can only be ordered using the time component of the Calendar Date Column. To order the day's events by other values like the event type and employee name, we have to generate time offsets using the desired ordering. Analytic rank functions are useful for doing this. The other problem is that we can't use aggregation to group the vacation events under a  single heading as each vacation event must be returned as a separate row so they can be rendered as individual links in the Calendar.

     

    I've recreated the Calendar on a new page (222). The original query returns more columns than necessary for a Calendar region: some of the additional columns are used as URL parameters in the event links. The unused ones have been removed to simplify things.

     

    To get the events in order, a sequence number is generated for each day's events, ordered by event type and employee name, with vacation events at the end. This sequence number is then converted to seconds and added to the event date as a time offset to provide the required order.

     

    Grouping vacation events under a single heading has to occur after APEX has rendered the Calendar, so jQuery is used to manipulate the event elements in the DOM. To make this easier the "Vacation" event description is suppressed, and the vacation event employee description wrapped in a span element that can easily be used as a jQuery selector.

     

    with calendar as (
        select
            ve.on_event_date
            /*
              For "Vacation" events, suppress the description, and wrap the employee info
              in a <span> for use as a jQuery selector.
            */
          , case ve.act_desc
              when 'Vacation' then '<span class="vacation">' || e.full_name || '</span>'
              else ve.act_desc || nvl2(e.full_name, ' - ', null) || e.full_name
            end show
          , ve.event_dates_id
          , ve.event_id
          , ve.employee_id
            /*
              Generate the ordinal number for the day's events when ordered by event type
              and employee name.
            */
          , dense_rank()
              over (
                partition by ve.on_event_date
                order by  nullif(ve.act_desc, 'Vacation') nulls last
                        , e.full_name ) seq
        from
            view_event_calendar_master_act ve
              left outer join employee e
                on ve.employee_id = e.employee_number)
    select
        /*
          Get events in the required order by using the ordinal numbers
          as a time offset in seconds from the start of the day.
        */
        on_event_date + numtodsinterval(seq, 'second') on_event_date
      , show
      , event_dates_id
      , event_id
      , employee_id
    from
        calendar

     

    A Dynamic Action is used to group each day's Vacation events as a description list:

     

    When

     

    Event: After Refresh

    Selection Type: Region

    Region: Calendar

     

    True Actions

     

    Action: Execute JavaScript Code

    Fire On Page Load:Yes

    Code

    $('.t13Calendar td')
      .each(
        function () {
          $(this)
            .find('.vacation')
            .closest('.apex_cal_data_grid_src')
            .wrapAll('<dl></dl>')
            .wrap('<dd></dd>')
            .closest('dd')
            .first()
            .before('<dt>Vacation</dt>');
        }
      );
  • 4. Re: Events in calendar formatting
    LathaVinnakota Newbie
    Currently Being Moderated

    Thanks a bunch. This is exactly what I am looking for, Can this be done for any type of activity, right now, it is filtered on vacation. There will be other activities like conferences and training that multiple employees will attend

     

    Please let me know

     

    Thanks

    Latha

  • 5. Re: Events in calendar formatting
    LathaVinnakota Newbie
    Currently Being Moderated

    I am using a query which is working in SQL developer,but not in APEX

     

    SELECT act_desc, ON_EVENT_DATE, listagg (employee_id, ',') WITHIN GROUP (ORDER BY employee_id) full_name FROM view_event_calendar_master_act GROUP BY act_desc, ON_EVENT_DATE;

     

    Thanks

    Latha

  • 6. Re: Events in calendar formatting
    Ramani_vadakadu Journeyer
    Currently Being Moderated

    can you try SQL Workshop at APEX, then try in IR report query!

    either Home>Util>QB

  • 7. Re: Events in calendar formatting
    fac586 Guru
    Currently Being Moderated

    LathaVinnakota wrote:

     

    Thanks a bunch. This is exactly what I am looking for, Can this be done for any type of activity, right now, it is filtered on vacation. There will be other activities like conferences and training that multiple employees will attend

    Yes, it can. I suggest that you extend your data model to support this, with entities and attributes that enable events to be categorised into generic types (meeting, conference, training, vacation etc) and flagged as having multiple attendees. These categories and flags can then be used as HTML classes to provide the selectors necessary for CSS styling, and jQuery behaviours like that above.

  • 8. Re: Events in calendar formatting
    fac586 Guru
    Currently Being Moderated

    LathaVinnakota wrote:

     

    I am using a query which is working in SQL developer,but not in APEX

     

    SELECT act_desc, ON_EVENT_DATE, listagg (employee_id, ',') WITHIN GROUP (ORDER BY employee_id) full_name FROM view_event_calendar_master_act GROUP BY act_desc, ON_EVENT_DATE;

    In what way is it not working in APEX?

     

    As I said above:

    The other problem is that we can't use aggregation to group the vacation events under a  single heading as each vacation event must be returned as a separate row so they can be rendered as individual links in the Calendar.

    If you aggregate the employees on vacation on a particular day, like:

     

    Vacation - Allen, Ford, Scott

     

    then the APEX Calendar will only generate one link and you will not be able to drill into the individual vacation events for each employee.

     

    You can use aggregation if you don't use the Calendar Column Links and generate the links in the query yourself, but in my opinion it's always better to use built-in APEX features when available,


  • 9. Re: Events in calendar formatting
    fac586 Guru
    Currently Being Moderated

    Ramani_vadakadu wrote:

     

    can you try SQL Workshop at APEX, then try in IR report query!

    either Home>Util>QB

    Why would they "try in IR report query" when the requirement is to create a Calendar?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points