-- Group: projects
-- Name:  detailte
-- Notes: 
SELECT prj_id, prj_number, prj_name,
  CASE WHEN(prj_status='P') THEN <? value("planning") ?>
       WHEN(prj_status='O') THEN <? value("open") ?>
       WHEN(prj_status='C') THEN <? value("complete") ?>
       ELSE <? value("undefined") ?>
 END AS prj_status, 
 prj_username, 
  COALESCE(SUM(prjtask_hours_budget), 0.0) AS budget_hrs,
  COALESCE(SUM(prjtask_hours_actual), 0.0) AS actual_hrs,
  COALESCE(SUM(prjtask_hours_budget - prjtask_hours_actual), 0.0) AS balance_hrs, 
 CASE WHEN COALESCE(SUM(prjtask_hours_budget - prjtask_hours_actual), 0.0) < 0 THEN 
   'error' END AS balance_hrs_qtforegroundrole, 
   'qty' AS budget_hrs_xtnumericrole, 
   'qty' AS actual_hrs_xtnumericrole, 
   'qty' AS balance_hrs_xtnumericrole 
FROM prj
LEFT OUTER JOIN prjtask ON (prj_id=prjtask_prj_id)
<? if not exists("showComplete") ?>
WHERE (prj_status <> 'C')
<? endif ?>
GROUP BY prj_id, prj_number, prj_name, prj_status, prj_username 
 ORDER BY prj_number;