create or replace package body my_calendar_pkg as

function get_key_colour(p_key_code in varchar2, p_key_sql in varchar2) return varchar2
as
  TYPE t_key IS RECORD(
    key_code varchar2(20),
    key_name varchar2(200),
    key_colour varchar2(20) );
  lv_key t_key;

  TYPE t_cursor IS REF CURSOR;
  lv_cursor t_cursor;

  lv_key_sql varchar2(2000);
  lv_index number;
  lv_colour varchar2(20);

begin
  
  if p_key_code is not null then
    lv_key_sql := 'select * from (' || p_key_sql || 
      ') the_key';

    begin
      -- Open cursor
      OPEN lv_cursor FOR lv_key_sql;
      lv_index := 0;
      
      -- Fetch rows from result set one at a time:
      LOOP
        FETCH lv_cursor INTO lv_key;
        EXIT WHEN lv_cursor%NOTFOUND;
        lv_index := lv_index + 1;
        
        if lv_key.key_code = p_key_code then
          -- found a match!
          if lv_key.key_colour is not null then
            return lv_key.key_colour;
          else
            -- choose colour by index
            case lv_index 
              when 1 then return 'FF0000';
              when 2 then return '00FF00';
              when 3 then return '0000FF';
              when 4 then return '999900';
              when 5 then return '009999';
              when 6 then return '990099';
              else        return '000000';
            end case;

          end if;
        end if;
      END LOOP;
    exception
      when others then
        return '00CC00';
    end;
    return '0000CC';
  end if;
  return 'FF0000';
end;

procedure show
(
  p_start_date in date,
  p_end_date in date,
  p_time_scale in varchar2,
  p_data_sql in varchar2,
  p_key_sql in varchar2
)
as
  TYPE t_data IS RECORD(
    row_title varchar2(200),
    start_date date,
    end_date date,
    key_code varchar2(20),
    description varchar2(2000) );
  lv_data t_data;

  TYPE t_key IS RECORD(
    key_code varchar2(20),
    key_name varchar2(200),
    key_colour varchar2(20) );
  lv_key t_key;

  TYPE t_cursor IS REF CURSOR;
  lv_cursor t_cursor;

  lv_period date;
  lv_heading varchar2(50);
  lv_start_date date;
  lv_end_date date;
  lv_period_end date;
  lv_data_sql varchar2(2000);
  lv_key_sql varchar2(2000);
  lv_row_title varchar2(200);
  
begin
  --htp.p('Start Date is ' || to_date(p_start_date));
  --htp.p('Time scale is ' || p_time_scale);
  --htp.p('Data sql is ' || p_data_sql);
  --htp.p('Key sql is ' || p_key_sql);
  
  lv_start_date := nvl(p_start_date, sysdate);
  lv_end_date := nvl(p_end_date, lv_start_date + 28);
  
  htp.p('<table border=1 bordercolor="CCCCCC">');
  -- do table headings
  htp.p('<tr><th>&nbsp;</th>');
  lv_period := lv_start_date;
  loop
    if p_time_scale is null or substr(p_time_scale,1,1) = 'D' then 
      if to_char(lv_period, 'D') in ('6','7') then
        htp.p('<th bgcolor="CCCCCC" width=15 align=center>');
      else
        htp.p('<th  width=15 align=center>');
      end if;
      htp.p( to_char(lv_period, 'DD') || '</th>');
      lv_period := lv_period + 1;
    end if;
    if substr(p_time_scale,1,1) = 'W' then 
      htp.p('<th>' ||  to_char(lv_period, 'DD/MM') || '</th>');
      lv_period := lv_period + 7;
    end if;
    if substr(p_time_scale,1,1) = 'M' then 
      htp.p('<th>' ||  to_char(lv_period, 'MON YYYY') || '</th>');
      lv_period := add_months(lv_period,1);
    end if;
    if substr(p_time_scale,1,1) = 'Q' then 
      htp.p('<th>Q' ||  to_char(mod(to_number(to_char(lv_period, 'Q'))+2,4)+1) 
        || ' ' || to_char(add_months(lv_period,-3),'YYYY') || '/' 
        || to_char(add_months(lv_period,9),'YYYY') || '</th>');
      lv_period := add_months(lv_period,3);
    end if;
    if substr(p_time_scale,1,1) = 'Y' then 
      htp.p('<th>' ||  to_char(add_months(lv_period,-3),'YYYY') || '/' 
        || to_char(add_months(lv_period,9),'YYYY') || '</th>');
      lv_period := add_months(lv_period,12);
    end if;
    --htp.p('<th>' || lv_period || '</th>');
    exit when lv_period > lv_end_date;
  end loop;
  htp.p('</tr>');
  
  -- now do the table data
  lv_data_sql := 'select * from (' || p_data_sql || 
    ') the_data where end_date > :v_start_date and start_date < :v_end_date';
  
  --htp.p('lv_data_sql: ' || lv_data_sql);
  lv_row_title := '';
  lv_period := lv_start_date;
  
  -- Open cursor
  OPEN lv_cursor FOR lv_data_sql || ' order by row_title, start_date'
    USING lv_start_date, lv_end_date;

  -- Fetch rows from result set one at a time:
  LOOP
    FETCH lv_cursor INTO lv_data;
    EXIT WHEN lv_cursor%NOTFOUND;
    --htp.p('<tr><td>' || nvl(lv_data.row_title, '&nbsp;') || lv_row_title || '</td>');
    --lv_period := lv_start_date;
    
    if upper(lv_data.row_title) != upper(lv_row_title) then -- change in row
      -- this is the start of a new row
      lv_row_title := lv_data.row_title;
      --htp.p('New title');
      -- finish off the old row
      if trunc(lv_period) > trunc(lv_start_date) then
        if lv_period <= lv_end_date then
          loop
            if p_time_scale is null or substr(p_time_scale,1,1) = 'D' then 
              if to_char(lv_period, 'D') in ('6','7') then
                htp.p('<td bgcolor="CCCCCC">');
              else
                htp.p('<td>');
              end if;
              htp.p( '&nbsp;</td>');
              lv_period := lv_period + 1;
            end if;
            if substr(p_time_scale,1,1) = 'W' then 
              htp.p('<td>&nbsp;</td>');
              lv_period := lv_period + 7;
            end if;
            if substr(p_time_scale,1,1) = 'M' then 
              htp.p('<td>&nbsp;</td>');
              lv_period := add_months(lv_period,1);
            end if;
            if substr(p_time_scale,1,1) = 'Q' then 
              htp.p('<td>&nbsp;</td>');
              lv_period := add_months(lv_period,3);
            end if;
            if substr(p_time_scale,1,1) = 'Y' then 
              htp.p('<td>&nbsp;</td>');
              lv_period := add_months(lv_period,12);
            end if;
            --htp.p('<th>' || lv_period || '</th>');
            exit when lv_period > lv_end_date;
          end loop;
        end if;
        htp.p('</tr>');
      end if;
      -- now start the new row
      htp.p('<tr><td>' || nvl(lv_data.row_title, '&nbsp;') || '</td>');
      lv_period := lv_start_date;

    else -- end of change in row
      
      if lv_row_title is null then -- first row
        htp.p('<tr><td>' || nvl(lv_data.row_title, '&nbsp;') || '</td>');
        lv_row_title := lv_data.row_title;
        lv_period := lv_start_date;
      end if; -- end of first row
      
    end if; 
    
    if lv_period <= lv_data.end_date then
      -- the selected item is not finished - we need to display it...
      
      loop
        -- calculate when this period ends
        if p_time_scale is null or substr(p_time_scale,1,1) = 'D' then 
          lv_period_end := lv_period + 1;
        end if;
        if substr(p_time_scale,1,1) = 'W' then 
          lv_period_end := lv_period + 7;
        end if;
        if substr(p_time_scale,1,1) = 'M' then 
          lv_period_end := add_months(lv_period,1);
        end if;
        if substr(p_time_scale,1,1) = 'Q' then 
          lv_period_end := add_months(lv_period,3);
        end if;
        if substr(p_time_scale,1,1) = 'Y' then 
          lv_period_end := add_months(lv_period,12);
        end if;
      
        if lv_data.start_date > lv_period_end then

          -- the current item starts after this peiod ends, so nothing to show
          if (p_time_scale is null or substr(p_time_scale,1,1) = 'D') and to_char(lv_period, 'D') in ('6','7') then
            htp.p('<td bgcolor="CCCCCC">&nbsp;</td>');
          else
            htp.p('<td>&nbsp;</td>');
          end if;

        else

          -- the current item has started (although it might have finished)
          if lv_data.end_date > lv_period or lv_period > lv_end_date then

            -- it hasn't ended yet, so it must still be going on
            if (p_time_scale is null or substr(p_time_scale,1,1) = 'D') and to_char(lv_period, 'D') in ('6','7') then
              htp.p('<td bgcolor="CCCCCC">&nbsp;</td>');
            else
              htp.p('<td bgcolor=' || get_key_colour(lv_data.key_code, p_key_sql) 
                || ' title="' || lv_data.description || '">&nbsp;</td>');
            end if;

          else
          
            -- it has ended, so nothing more to do
            exit when true;

          end if;
        end if;
        
        -- moving right along...
        lv_period := lv_period_end;
      
      end loop;
    end if;
  
  END LOOP;

  -- Close cursor:
  CLOSE lv_cursor;

  -- fill in any remaining cells
  if lv_period < lv_end_date then

    loop
      if (p_time_scale is null or substr(p_time_scale,1,1) = 'D') and to_char(lv_period, 'D') in ('6','7') then
        htp.p('<td bgcolor="CCCCCC">');
      else
        htp.p('<td>');
      end if;
      htp.p( '&nbsp;</td>');

      if p_time_scale is null or substr(p_time_scale,1,1) = 'D' then 
        lv_period := lv_period + 1;
      end if;
      if substr(p_time_scale,1,1) = 'W' then 
        lv_period := lv_period + 7;
      end if;
      if substr(p_time_scale,1,1) = 'M' then 
        lv_period := add_months(lv_period,1);
      end if;
      if substr(p_time_scale,1,1) = 'Q' then 
        lv_period := add_months(lv_period,3);
      end if;
      if substr(p_time_scale,1,1) = 'Y' then 
        lv_period := add_months(lv_period,12);
      end if;
      exit when lv_period > lv_end_date;
    end loop;
  end if;

  
  htp.p('</tr></table>');

end show;

procedure show_key
(
  p_key_sql in varchar2
)
as
  TYPE t_key IS RECORD(
    key_code varchar2(20),
    key_name varchar2(200),
    key_colour varchar2(20) );
  lv_key t_key;

  TYPE t_cursor IS REF CURSOR;
  lv_cursor t_cursor;

  lv_key_sql varchar2(2000);
  lv_index number;
  lv_colour varchar2(20);

begin
  
  lv_key_sql := 'select * from (' || p_key_sql || 
    ') the_key';

  htp.p('<table border=0><tr><th width=15>&nbsp;</th><th>Key</th></tr>');
  -- Open cursor
  OPEN lv_cursor FOR lv_key_sql;
  lv_index := 0;
  
  -- Fetch rows from result set one at a time:
  LOOP
    FETCH lv_cursor INTO lv_key;
    EXIT WHEN lv_cursor%NOTFOUND;
    lv_index := lv_index + 1;
    
    if lv_key.key_colour is not null then
      lv_colour := lv_key.key_colour;
    else
      -- choose colour by index
      case lv_index 
        when 1 then lv_colour := 'FF0000';
        when 2 then lv_colour := '00FF00';
        when 3 then lv_colour := '0000FF';
        when 4 then lv_colour := '999900';
        when 5 then lv_colour := '009999';
        when 6 then lv_colour := '990099';
        else        lv_colour := '000000';
      end case;

    end if;
    
    htp.p('<tr><td bgcolor="' || lv_colour || '">&nbsp;</td><td>' ||
      lv_key.key_name || '</td></tr>');
  END LOOP;
  htp.p('</table>');

exception
  when others then
    null;
end;



end my_calendar_pkg;