9 Replies Latest reply: Oct 30, 2013 4:22 AM by fac586 RSS

    Events in calendar formatting

    LathaVinnakota

      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

          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

            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

              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

                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

                  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_apex

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

                    either Home>Util>QB

                    • 7. Re: Events in calendar formatting
                      fac586

                      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

                        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

                          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?