declare
   c integer := 0;
   d date;
   d2 date;
   ed date;
   d_count pls_integer := 0;
   x integer;
   l_start_cell number;
   l_col_span number;
   l_left_cells integer;
   l_ms varchar2(4000);
   l_proj varchar2(4000);
   l_complete_id number := null;
   l_weeks number := 22;
   l_months number;
   l_color varchar2(30) := null;
   l_title varchar2(4000);
   l_first_month  varchar2(100);
   l_last_month   varchar2(100);
   l_month_span integer;
   l_month_weeks integer;
begin

-- compute start date
d := trunc(sysdate)+(14 * l_weeks);
ed := d + (7 * l_weeks);

-- print headers
l_month_span := 0;
l_month_weeks := 0;
l_last_month := trim(to_char(d+6,'Month'));

sys.htp.prn('<table cellspacing="0" cellpadding="0" class="timelineTable">');

sys.htp.prn('<thead><tr>');
sys.htp.prn('<th></th>');
for i in 1..l_weeks loop
    d2 := d + ((i - 1)*7);
    l_first_month := trim(to_char(d2+6,'Month'));
    if l_first_month != l_last_month then
       htp.prn('<th class="monthLabel" colspan="'||l_month_span||'">'||l_last_month||'</th>');
       l_month_weeks := l_month_weeks + l_month_span;
       l_month_span := 0;
    end if;
    l_month_span := l_month_span + 1;
    l_last_month := l_first_month;
end loop;
if l_month_weeks < l_weeks then
    d2 := d + (7 * l_weeks) + 6;
    htp.prn('<th class="monthLabel" colspan="'||to_char(l_weeks - l_month_weeks)||'">'||
        to_char(d2,'Month')||'</th>');
end if;
sys.htp.prn('</tr>');



sys.htp.prn('<tr>');
sys.htp.prn('<th></th>');
for i in 1..l_weeks loop
    d2 := d + ((i - 1)*7);
    sys.htp.prn('<th class="weekLabel">'||to_char(d2,'DD')||'-'||to_char(d2+6,'DD')||'</th>');
end loop;
sys.htp.prn('</tr></thead><tbody class="hideMe">');


-- print row
for c1 in (
select distinct zadname milestone_name
from zadanie
where (nvl(:P38_NEW,'0') = '0' or zadname = :P38_NEW)
order by 1) loop

c := 0;

for c2 in (
select
   podzadid,
   a.zadname,
   podzadname milestone_name,
   statuszad    milestone_status,
   rukid milestone_owner,
   dnpodzad MILESTONE_START_DATE,
   dopodzad MILESTONE_DATE,
   to_char(dopodzad,'Month') the_month,
   to_char(dopodzad,'YYYY') the_year,
   to_char(dopodzad,'DD') the_day
from podzadachi, zadanie a
where a.zadname = c1.milestone_name and a.zadid = podzadachi.zadid
order by dopodzad
) loop

c := c + 1;
   if c = 1 then
--      sys.htp.prn('<tr>');
      if length(c1.milestone_name) > 50 then
         l_proj := substr(c1.milestone_name,1,50)||'...';
      else
         l_proj := c1.milestone_name;
      end if;
      sys.htp.prn('</tbody><tbody><tr><th><a href="#">'||
         sys.htf.escape_sc(l_proj)||'</a></th>');
   else
      sys.htp.p('<tr><th></th>');
   end if;

  -- compute starting cell
   if c2.MILESTONE_START_DATE <= d then
      l_start_cell := 1;
   else
      l_start_cell := (c2.MILESTONE_START_DATE - d) / 7;
   end if;
   l_start_cell := ceil(l_start_cell);
   
   -- before milestone
   if l_start_cell > 1 then
      l_left_cells := l_start_cell - 1;
      for j in 1..l_left_cells loop
          sys.htp.prn('<td></td>');
      end loop;
   else
      l_left_cells := 0;
   end if;
-- complete milestone length
   if (c2.MILESTONE_DATE - greatest(c2.MILESTONE_START_DATE,d)) < 7 then
      l_col_span := 1;
   else
      l_col_span := (c2.MILESTONE_DATE - greatest(c2.MILESTONE_START_DATE,d)) / 7;
   end if;
   l_col_span := ceil(l_col_span);
   
   l_ms := substr(c2.milestone_name,1,greatest((l_col_span * 15),22));
   if length(l_ms) < length(c2.milestone_name) then
      l_ms := l_ms ||'...';
   end if;
   -- color
   if upper(nvl(c2.milestone_status, 50)) = 50 then
       l_color := 'completedMilestone';
   elsif upper(nvl(c2.milestone_status,75)) = 75 and c2.MILESTONE_DATE >= trunc(sysdate) then
       l_color := '';
   elsif upper(nvl(c2.milestone_status,100)) = 100 and c2.MILESTONE_DATE < trunc(sysdate) then
       l_color := 'lateMilestone';
   else
       l_color := 'brown';
   end if;

l_title :=  trim(to_char(c2.MILESTONE_START_DATE,'DD Month')) ||' - '||
       trim(to_char(c2.MILESTONE_DATE,'DD Month'));
   if c2.milestone_owner is not null then
      l_title := l_title||' - '||sys.htf.escape_sc(c2.milestone_owner);
   end if;
   
   sys.htp.prn('<td colspan="'||l_col_span||'">');
   sys.htp.prn('<a href="#" class="'||l_color||'">');
   sys.htp.prn(sys.htf.escape_sc(l_ms)||'</a></td>');
     
     
   -- fill to right
   x := l_weeks - (l_left_cells + l_col_span);
   if x > 0 then
      for j in 1..x loop
         sys.htp.p('<td></td>');
      end loop;
   end if;
     
   sys.htp.prn('</tr>');
end loop; -- inner loop

sys.htp.prn('</tbody>');

end loop; -- outer loop



-- class values are green red blue brown

-- close table
sys.htp.prn('</table>');

end;

for c1 in (
select distinct a.zadname milestone_name
from zadanie a
where (nvl(:P38_NEW,'0') = '0' or a.zadname = :P38_NEW)
order by 1) loop

c := 0;

for c2 in (
select
   podzadid,
   a.zadname,
   podzadname milestone_name,
   statuszad    milestone_status,
   rukid milestone_owner,
   DNPODZAD MILESTONE_START_DATE,
   DOPODZAD MILESTONE_DATE,
   to_char(dopodzad,'Month') the_month,
   to_char(dopodzad,'YYYY') the_year,
   to_char(dopodzad,'DD') the_day
from podzadachi, zadanie a
where a.zadname = c1.milestone_name and a.zadid = podzadachi.zadid
order by dopodzad
) loop